Re: Getting Oriented: Political versus Ecological Geography
--- Peter Brawley [EMAIL PROTECTED] wrote: David, If one finds oneself thinking about creating large numbers of columns for particular features, the working model is likely not general enough (eg why not a child table that permits you to add any number of features you please for any number of countries or provinces or whatever, and a featureTypes table to handle repeating feature types?). As a general rule of thumb, I think you can expect to throw your first few models away. Your project sounds huge, so many geofeatures are changing, so many overlap. Rather than starting from scratch, did you check out already existing solutions, eg arcGIS (http://support.esri.com/index.cfm?fa=downloads.dataModels.gateway)? Good luck. Wow, that's an awesome resource. Coincidentally, I downloaded ArcExplorer just a few hours ago, but I've just begun to learn about GIS. Thanks for the tip. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cluster and Fulltext indexes?
At 15:21 -0500 29-05-2004, mos wrote: At 01:39 PM 5/29/2004, you wrote: Hello, Does the cluster support fulltext indexes? After a quick reading of preliminary documentation it seems NO. Can someone confirm it? Thank you. Santino Cusimano - Santino, Where does it say that in the docs? Can you post the documentation that says fulltext indexes won't be implemented in clusters? Mike I think that won't be implemented in clusters is not correct. I suggest are not yet implemented in cluster. Fulltext indexes are implemented only in MyIsam table and the cluster is another type of table (NDB) . In a PDF file at: http://dev.mysql.com/get/Downloads/Manual/mysql-cluster-admin.pdf Section 7 NDB Sql: Page 46: No text fields only varchar Page 48-49 select features: No match ... against Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Queries (specifically with LIMIT)
I found it interesting. Thanks Eric. (reconstructed from archives i accidentally deleted the copy in my mailbox Daniel Clark writes: I don't see how LIMIT would make a difference. LAST_INSERT_ID() only returns one record. But it's worth trying in a big loop to get timing numbers. Well, I decided to test this all out and see what happened. From what my little test was able to determine, there is no difference in speed from doing: SELECT LAST_INSERT_ID(); vs SELECT LAST_INSERT_ID() LIMIT 1; I used 100,000 selects as my benchmark number (which on my box here took about 40 seconds per run to complete). Times were measured using unix time, and I averaged the user times and the difference was so small as to be negligable, then i decided to measure the same thing but with SELECT LAST_INSERT_ID() FROM table; and it was approximately 25% slower (but im assuming as the table grew in length so would the gap in speed. Conclusion: it makes no difference positive or negative including the LIMIT, but as Michael pointed out including the FROM clause causes a big penalty. I hope someone else finds this slightly interesting. For completeness im including the program i wrote to test this. Your mileage may vary, it needs a little configuring for your particular setup before you can run tests. (database name, user/password etc) you have to manually change the one line inside the loop to call whichever function you want to test. And its also handy to change the one print statement before the loop. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting Oriented: Political versus Ecological Geography
Yeap, that's the idea. That's call normalization. If you are interested in database design, you need to read Codd's book in database. If you want, you I can help you looking at the database you build and give you some pointers. Osvaldo Sommer -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, May 29, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: RE: Getting Oriented: Political versus Ecological Geography Hm... I think you were an even bigger help than I anticipated! If I understand correctly, I need to fix the tables I've already created. Consider three tables - Continents, Nations and States - which look something like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us (code for the U.S.) | cna (links U.S. to North America in the Continents table) STATES Alaska | Juneau | ak (code for Alaska) | us (links Alaska to the U.S. in the Nations table) If I understand correctly, it would be smarter to create FOUR tables, that look like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us STATES Alaska | Juneau | ak FOURTH TABLE (Links everything together) cna | us | ak (North America U.S. Alaska) cna | us | az (North America U.S. Arizona) cna | us | hi (North America U.S. Hawaii) cna | us | wy (North America U.S. Wyoming) cna | ca | ab (North America Canada Alberta) caf | ken | (NULL) (Africa Kenya) caf | tan | (NULL) (Africa Tanzania) This table would have just four fields (columns) and would begin with about 250 rows - one for each nation - with another 50 rows for the fifty U.S. states, then more rows for Canada's provinces, Mexico's states, etc. If I'm on the right track, then I could also add U.S. counties to the mix... North America United States States Counties However, since there are roughly 3,000 counties, it might be better to put them in a separate table, with rows that might look like this: cna (North America) | sd (South Dakota) | Tripp (county) cna | sd | Melette cna | sd | Sioux cna | sd | Belle Fourche Or would you advise adding the counties to the Fourth Table, which would look something like this?: cna | us | sd | Belle Fourche ccna | us | sd | Sioux cna | us | sd | Trippe cna | ca | ab | (NULL) ceu | fra | (NULL) caf | ken | (NULL) | (NULL) The top row = North America U.S. South Dakota Belle Fourche County The last row = Africa Kenya, with the rows corresponding to states/provinces and U.S. counties left NULL. I just thought of one problem, though - there are several U.S. counties that have the same name. For example, several states have a Washington County. But maybe I could just give all the counties numerical codes, or something like this - tx-1 (for Texas' first county). I wish I'd thought about this earlier. It sounds a lot better than my original plan! Thanks. --- Osvaldo Sommer [EMAIL PROTECTED] wrote: For what i understand, what you need to do is create this structure: Characteristic K Char_Code Char_Description Continent K Con_Code Con_Description Country K Cot_Code Cot_Description Cot_Continent ( This is the code of a continent in the table continent) Country_Charact K Des_Country ( This is the code of a country in the table country) K Des_Characteristic ( This is the code of a characteristic in the table characteristic) This way you can define the characteristics 1 time and assign to a country as many or as few as you need. You may want to use innob tables to create the foreing key and to help them inforce them. Hope this is usefull Osvaldo Sommer -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, May 29, 2004 5:57 PM To: [EMAIL PROTECTED] Subject: Re: Getting Oriented: Political versus Ecological Geography --- Peter Brawley [EMAIL PROTECTED] wrote: In the states table, should I list Colorado's regions in three cells... Great Plains | Rocky Mountains | Colorado Plateau, or group them in one cell, like this: Colorado | state | grasslands, Rocky Mountains, Colorado Plateau | co | cna | 5 It gets even trickier, because I may link Colorado to several regions, including ecological regions, physiographic provinces and political. Codd's first axiom is that all values shall be atomic (yes, mysql sets enums break that rule). Putting your little set 'grasslands, mountains, plateau' in one column would make queries on those items awkward. You might want to bone up on normalisation (viz links at http://www.artfulsoftware.com/dbresources.html). Thanks for the link. I've been studying some of the resources. I have an idea for another approach. If putting grasslands, forests, mountains in one cell is bad practice, then I may wind up with a table with hundreds of
Where is Release mysql-4.1.2a-alpha-win.zip
Hi, searching for latest MySQL-Downloads I see there's no mysql-4.1.2a-alpha-win.zip available by now on - for example - ftp://ftp.fh-wolfenbuettel.de/pub/database/mysql/Downloads/MySQL-4.1/. http://dev.mysql.com/downloads/mysql/4.1.html links the file mysql-4.0.20a-win-noinstall.zip in the MySQL-4.1 directory on MySQL-Download FTP-Servers as 4.1 release without installe. Is that correct? Best, Bernhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is Release mysql-4.1.2a-alpha-win.zip
On Sun, May 30, 2004 at 06:58:35PM +0200, Bernhard Döbler wrote: searching for latest MySQL-Downloads I see there's no mysql-4.1.2a-alpha-win.zip available by now on - for example - ftp://ftp.fh-wolfenbuettel.de/pub/database/mysql/Downloads/MySQL-4.1/. As Patrick's release announcement (http://lists.mysql.com/announce/199) said, binaries for Microsoft Windows are not yet available for 4.1.2. http://dev.mysql.com/downloads/mysql/4.1.html links the file mysql-4.0.20a-win-noinstall.zip in the MySQL-4.1 directory on MySQL-Download FTP-Servers as 4.1 release without installe. Is that correct? No, some files were simply copied into the wrong directory, and thus listed on the wrong page. Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting Oriented: Political versus Ecological Geography
--- Osvaldo Sommer [EMAIL PROTECTED] wrote: Yeap, that's the idea. That's call normalization. If you are interested in database design, you need to read Codd's book in database. I thought I had a natural talent for database design, since I've done so much work with data, but it isn't as simple as I thought. If you want, you I can help you looking at the database you build and give you some pointers. That would be great, thanks. But I'll probably spend a few days creating a new database first. I'd really like to figure out how to access GIS files and extract their data into MySQL tables. I've been told you can do it with Microsoft Excel, but I just have the Microsoft Works spreadsheet. (I should have spent the extra $50 when I bought my computer?) It looks like all the information I need has probably been organized by the GIS community, but that's another technology to learn about. Osvaldo Sommer -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, May 29, 2004 9:39 PM To: [EMAIL PROTECTED] Subject: RE: Getting Oriented: Political versus Ecological Geography Hm... I think you were an even bigger help than I anticipated! If I understand correctly, I need to fix the tables I've already created. Consider three tables - Continents, Nations and States - which look something like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us (code for the U.S.) | cna (links U.S. to North America in the Continents table) STATES Alaska | Juneau | ak (code for Alaska) | us (links Alaska to the U.S. in the Nations table) If I understand correctly, it would be smarter to create FOUR tables, that look like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us STATES Alaska | Juneau | ak FOURTH TABLE (Links everything together) cna | us | ak (North America U.S. Alaska) cna | us | az (North America U.S. Arizona) cna | us | hi (North America U.S. Hawaii) cna | us | wy (North America U.S. Wyoming) cna | ca | ab (North America Canada Alberta) caf | ken | (NULL) (Africa Kenya) caf | tan | (NULL) (Africa Tanzania) This table would have just four fields (columns) and would begin with about 250 rows - one for each nation - with another 50 rows for the fifty U.S. states, then more rows for Canada's provinces, Mexico's states, etc. If I'm on the right track, then I could also add U.S. counties to the mix... North America United States States Counties However, since there are roughly 3,000 counties, it might be better to put them in a separate table, with rows that might look like this: cna (North America) | sd (South Dakota) | Tripp (county) cna | sd | Melette cna | sd | Sioux cna | sd | Belle Fourche Or would you advise adding the counties to the Fourth Table, which would look something like this?: cna | us | sd | Belle Fourche ccna | us | sd | Sioux cna | us | sd | Trippe cna | ca | ab | (NULL) ceu | fra | (NULL) caf | ken | (NULL) | (NULL) The top row = North America U.S. South Dakota Belle Fourche County The last row = Africa Kenya, with the rows corresponding to states/provinces and U.S. counties left NULL. I just thought of one problem, though - there are several U.S. counties that have the same name. For example, several states have a Washington County. But maybe I could just give all the counties numerical codes, or something like this - tx-1 (for Texas' first county). I wish I'd thought about this earlier. It sounds a lot better than my original plan! Thanks. --- Osvaldo Sommer [EMAIL PROTECTED] wrote: For what i understand, what you need to do is create this structure: Characteristic K Char_Code Char_Description Continent K Con_Code Con_Description Country K Cot_Code Cot_Description Cot_Continent ( This is the code of a continent in the table continent) Country_Charact K Des_Country ( This is the code of a country in the table country) K Des_Characteristic ( This is the code of a characteristic in the table characteristic) This way you can define the characteristics 1 time and assign to a country as many or as few as you need. You may want to use innob tables to create the foreing key and to help them inforce them. Hope this is usefull Osvaldo Sommer -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Saturday, May 29, 2004 5:57 PM To: [EMAIL PROTECTED] Subject: Re: Getting Oriented: Political versus Ecological Geography --- Peter Brawley [EMAIL PROTECTED] wrote: In the states table, should I list Colorado's regions in three cells... Great Plains | Rocky Mountains | Colorado
Stupid newbie question - adding comments for columns tables?
Apologies for what is a pitifully simple question from someone who has just started using MySQL 4.0. I've looked in the documentation and elsewhere, but can't find the exact syntax for adding comments to columns when creating a table. My attempts at guessing the syntax so far have failed. Tips, hints, examples? Thanks-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid newbie question - adding comments for columns tables?
Randy Burgess [EMAIL PROTECTED] wrote: Apologies for what is a pitifully simple question from someone who has just started using MySQL 4.0. I've looked in the documentation and elsewhere, but can't find the exact syntax for adding comments to columns when creating a table. My attempts at guessing the syntax so far have failed. Tips, hints, examples? Thanks-- Comment on the column level is supported from version 4.1. CREATE TABLE t1(id int COMMENT 'comment'); -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password displayed in process list
Hi, I am new to MySQL and have one question. I would like to run mysql job as a batch job (scheduled through cron) and have to provide password to the batch job: mysql -udba -ppassword My concern that anybody running: ps -ef will be able to see the password when job is run. Is there any way to run mysql as a batch job with mysql -udba -p and pump a password from inside your script. Something like: mysql -udba -p EOF .hidden_pwd_file use mysql select * EOF I've tried a few things but I am out of ideas. Any help would be appreciated. Alek - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
Re: Password displayed in process list
Alek, Create a file of any name, e.g. script.cnf. Put the following in it, [client] user = dba password = dba_pass Run mysql with the --defaults-file arg mysql --defaults-file=script.cnf So long as the cnf file is only readable by the the cronjob owner this will provide the level of security you require. Richard. For more info see, http://dev.mysql.com/doc/mysql/en/Option_files.html - Original Message - From: Aleksandar Mihajlovic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, May 30, 2004 8:03 PM Subject: Password displayed in process list Hi, I am new to MySQL and have one question. I would like to run mysql job as a batch job (scheduled through cron) and have to provide password to the batch job: mysql -udba -ppassword My concern that anybody running: ps -ef will be able to see the password when job is run. Is there any way to run mysql as a batch job with mysql -udba -p and pump a password from inside your script. Something like: mysql -udba -p EOF .hidden_pwd_file use mysql select * EOF I've tried a few things but I am out of ideas. Any help would be appreciated. Alek - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.2 is released
Hi! Long-awaited MySQL-4.1.2 was released today. Windows binaries of 4.1.2 will be released as soon as they are available. The binaries and source are available for download at: http://dev.mysql.com/downloads/mysql/4.1.html InnoDB is a MySQL table type that provides foreign key constraints, transactions, row level locking, a MVCC concurrency control method for transactions, and a non-free hot backup tool that can take binary backups of your database without disturbing normal processing. MySQL-4.1.2 is mainly a bugfix release, but there are also a few important new features in InnoDB. The most important new feature is that InnoDB now supports multiple character sets in the same installation. For example, one column in a table can be in the default latin1_swedish_ci character set / collation, while another column is in UTF-8 and in some other collation order. This capability came to MyISAM tables already in earlier 4.1 releases, but for InnoDB the feature was completed in 4.1.2. Another new feature is that MySQL now automatically creates an index on a FOREIGN KEY if the user does not specify a suitable index for it. A suitable index is one where the foreign key columns appear in the index specification as the first columns, and are in the same order as in the FOREIGN KEY specification. Automatical creation will eliminate most of the annoying cases of Error 1005 (errno 150) in table creation, when the user forgot to specify a suitable index. Note that MySQL still does not automatically create an index on the REFERENCED key in the parent table. But that is not as big a problem, because usually the referenced key is the PRIMARY KEY of the parent table, and an index always exists on it. For more information on InnoDB foreign key constraints, see: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html The complete changelog of 4.1.2: Functionality added or changed: * Support multiple character sets. Note that tables created in other collations than latin1_swedish_ci cannot be accessed in MySQL/InnoDB 4.0. * Automatically create a suitable index on a FOREIGN KEY, if the user does not create one. Removes most of the cases of Error 1005 (errno 150) in table creation. * Do not assert in `log0log.c', line 856 if ib_logfiles are too small for innodb_thread_concurrency. Instead, print instructions how to adjust `my.cnf' and call exit(1). * If MySQL tries to SELECT from an InnoDB table without setting any table locks, print a descriptive error message and assert; some subquery bugs were of this type. * Allow a key part length in InnoDB to be up to 3,500 bytes; this is needed so that one can create an index on a column with 255 UTF-8 characters. * All new features from InnoDB-4.0.17, InnoDB-4.0.18, InnoDB-4.0.19 and InnoDB-4.0.20. Bugs fixed: * All bug fixes from InnoDB-4.0.17, InnoDB-4.0.18, InnoDB-4.0.19 and InnoDB-4.0.20. * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom was a printout like below in the `.err' log. (Bug fix from 4.0.21.) InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex * Improved portability to 64-bit platforms, especially Win64. * Fixed an assertion failure when a purge of a table was not possible because of missing `.ibd' file. * Fixed a bug: do not retrieve all columns in a table if we only need the 'ref' of the row (usually, the PRIMARY KEY) to calculate an ORDER BY. (Bug #1942) * On Unix-like systems, obtain an exclusive advisory lock on InnoDB files, to prevent corruption when multiple instances of MySQL are running on the same set of data files. The Windows version of InnoDB already took a mandatory lock on the files. (Bug #3608) * Added a missing space to the output format of SHOW INNODB STATUS; reported by Jocelyn Fournier. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: 4.0.20 for Windows - WHEN???
Anyone know when we can expect 4.0.20 for Windows? Also, is there a timescale for a beta version of 4.1? Cheers Andrew P. Andrew Pattison mail at apattison.plus.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Daemon Fails to start
Hello all, I am currently running mysql on a SuSE 9.0 machine. here is the mysql version Server version 4.0.15 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 23 min 0 sec The problem I'm having is that whenever I start the service rcmysql start or directly from /etc/init.d/mysql it fails to start. the weird thing is that I can access the databases and login to mysql. Any idea on what I can try to correct this problem and get the daemon to run properly? I've also done this: mysqladmin -u root -p ping and it's returned mysqld is alive When I run the command ps -e I get three instances of mysqld and one of mysqld_safe. This safe instance worries me a little. thanks marshall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing an auto_increment value in innodb
Gerben Gieling wrote: Dear all, I have an auto_increment value in an innodb table. I recently introduced a new row by mistake and removed it. I beleive I read somewhere in the manual that an innodb table does not reuse this number by default. I also remember vaguely that it is possible to reset this next autoincrement value (I want the next insert to reuse this number). I cannot find it in the manual now I need it. I'm only on the digest list so please (b)cc to me directly. Gerben Gieling Patent Information Specialist I had to do this the other day. I *think* I used the command: alter table table_name auto_increment=new_auto_increment_value; Although I can't remember the exact method I used ( I found a few options ), I remember that I had to change the table to MyISAM before it would accept the change, and then change it back to InnoDB later ... so this obviously isn't ideal if you have foreign keys set up. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specifying an index length and the default value
Hi David, Great questions: - Original Message - From: David Griffiths Sent: Friday, May 28, 2004 6:05 PM Subject: Specifying an index length and the default value The length of indexes on varchar and char indexes can be specified at index creation. What is the default length of an index if no length is provided? The default is to index the whole column length (of course that's not possible with TEXT/BLOB columns). The High Performance MySQL book hints that the index-length used is specific for each entry in the indexed column (ie an index on a column where the average length of the data is 8 bytes would take up (8 x number-of-rows) bytes). Well, maybe. It depends... see below. If a column was a varchar(128), would the index use 128 bytes per entry, or would it use the number of bytes in each row of the indexed column. So if each row had exactly four characters, the index would use four bytes per row, but if a row was added with 8 characters, that one row would have an index entry that was 8 bytes in size and the rest would remain at 4? I don't know that it's in the manual anywhere, but from experimenting and stuff, I've found that, generally, indexes with a TOTAL length (if there's multiple columns) of = 8 are fixed-length. That is, they'll always use 8 bytes/row even if a string doesn't take up that much space. Actually, this up to 8 bytes, fixed length behavior might only occur with indexed character columns (e.g. 100% numeric indexes may stay fixed-length regardless of their size). I'm not sure... You can change the behavior, however, by setting PACK_KEYS in ALTER/CREATE TABLE. The default, in MySQL 4+, is DEFAULT, where MySQL decides whether to use fixed-length keys (faster) or packed variable-length keys (space saving) depending on the index. Setting PACK_KEYS to 0 forces all fixed-length keys (I *think* 0 in 3.23 is like DEFAULT in 4+). Setting PACK_KEYS to 1 packs all *non-unique* indexes, even if they're numeric or = 8 bytes. It's useful to use myisamchk to find out the properties of columns in an index: myisamchk -dv /path/to/db/table In the second half of the output, you will see information about the table's indexes. Some things you may see in the Type column are: packed - I think this is for character indexes that have prefix compression (multiple index entries that start with the same characters are compressed). Any unused space at the end of the index (storing 10 chars in a 32 character index) is also not stored (like you were talking about above). prefix - I think this one is for numeric indexes that have prefix compression (in an INT index, values 0 - 255 use the same 3 bytes, so those can be compressed). stripped - This is for character indexes that have unused trailing space stripped (again, like you were talking about above). Now, having said that, there's still some things in the myisamchk output that I can't figure out: like sometimes there will be packed and stripped on the same column; sometimes not. And other things I can't remember now that don't seem consistent. I just kinda figured it out on my own since I don't know that there are official descriptions anywhere. ( But at least it gives you more of an idea of what's going on internally than you can get from a SQL query. :-) Thanks for any input. David. Hope that helps somewhat. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting first initials from an entire table's data (Slow query)
I'm trying to come up with an efficient way to query my table of names for all first initials. I.e. There are 50,000 names, and I want a result of: A, B, C, F, H, I, J, K... That is, a list of all first initials that are actually present in the data (and ideally are also used in a joined table). I haven't been able to think of a way to do this efficiently. My current query looks like this: select DISTINCT UPPER(LEFT(n.Name,1)) as Initial from Names n, Things t where n.ID = t.ID order by Initial desc Even if I eliminate DISTINCT, or create a single character index on Name, or create a whole field that just has the first character of Name, I can't figure out how to get MySQL to not have to scan the entire table. I get an EXPLAIN that looks like this: +---+--+---+---+-+-+---+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+-+---+---+ | n | ALL | PRIMARY,ID | NULL |NULL | NULL| 57674 | Using temporary; Using filesort | | t | ref | ID | ID | 5 | n.ID | 4 | where used; Using index; Distinct | +---+--+---+---+-+-+---+---+ Is there any way to do this, or an efficient way to query the table 26+ times with a list of first initials? (My actual query examines 166,000 rows and takes 12 seconds to run, all to give me a list of most of the alphabet!) Thanks in advance, TK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specifying an index length and the default value
Matt, Thanks for the great reply. We have a database that has been migrated over to MySQL 4.0, and the column-definitions are a bit wacked - way more space is allocated than is needed for many columns. I think you are using MyISAM tables; we are using InnoDB. I think it woudl be worth dropping some indexes after seeing how much free tablespace there is, and then re-creating those indexes with a length specifier of the column size, and see if the amount of free tablespace changes. If it doesn't, then MySQL is indexing the whole column. The question arose due to a unique index we have on a table; we can't seem to get the data in from our other database - the index fails, yet the value that it fails on appears only once in the table we are copying from. I wondered if maybe MySQL was defaulting the length of the index, and causing it to conflict with an other value. I don't believe it is, however, as there is no value that is even remotely similar. Regardless, I think it might be a worthwhile exercise to take a close look at our data, and see if we can guesstimate appropriate lengths. Thanks, David. Matt W wrote: Hi David, Great questions: - Original Message - From: David Griffiths Sent: Friday, May 28, 2004 6:05 PM Subject: Specifying an index length and the default value The length of indexes on varchar and char indexes can be specified at index creation. What is the default length of an index if no length is provided? The default is to index the whole column length (of course that's not possible with TEXT/BLOB columns). The High Performance MySQL book hints that the index-length used is specific for each entry in the indexed column (ie an index on a column where the average length of the data is 8 bytes would take up (8 x number-of-rows) bytes). Well, maybe. It depends... see below. If a column was a varchar(128), would the index use 128 bytes per entry, or would it use the number of bytes in each row of the indexed column. So if each row had exactly four characters, the index would use four bytes per row, but if a row was added with 8 characters, that one row would have an index entry that was 8 bytes in size and the rest would remain at 4? I don't know that it's in the manual anywhere, but from experimenting and stuff, I've found that, generally, indexes with a TOTAL length (if there's multiple columns) of = 8 are fixed-length. That is, they'll always use 8 bytes/row even if a string doesn't take up that much space. Actually, this up to 8 bytes, fixed length behavior might only occur with indexed character columns (e.g. 100% numeric indexes may stay fixed-length regardless of their size). I'm not sure... You can change the behavior, however, by setting PACK_KEYS in ALTER/CREATE TABLE. The default, in MySQL 4+, is DEFAULT, where MySQL decides whether to use fixed-length keys (faster) or packed variable-length keys (space saving) depending on the index. Setting PACK_KEYS to 0 forces all fixed-length keys (I *think* 0 in 3.23 is like DEFAULT in 4+). Setting PACK_KEYS to 1 packs all *non-unique* indexes, even if they're numeric or = 8 bytes. It's useful to use myisamchk to find out the properties of columns in an index: myisamchk -dv /path/to/db/table In the second half of the output, you will see information about the table's indexes. Some things you may see in the Type column are: packed - I think this is for character indexes that have prefix compression (multiple index entries that start with the same characters are compressed). Any unused space at the end of the index (storing 10 chars in a 32 character index) is also not stored (like you were talking about above). prefix - I think this one is for numeric indexes that have prefix compression (in an INT index, values 0 - 255 use the same 3 bytes, so those can be compressed). stripped - This is for character indexes that have unused trailing space stripped (again, like you were talking about above). Now, having said that, there's still some things in the myisamchk output that I can't figure out: like sometimes there will be packed and stripped on the same column; sometimes not. And other things I can't remember now that don't seem consistent. I just kinda figured it out on my own since I don't know that there are official descriptions anywhere. ( But at least it gives you more of an idea of what's going on internally than you can get from a SQL query. :-) Thanks for any input. David. Hope that helps somewhat. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyODBC - SQLColAttribute with SQL_DESC_OCTET_LENGTH parameter
Hello, In my application, I am connecting to various RBDMS but primarily SQL Server and MySQL using ODBC 3.0 driver. In SQL Server, if I execute SQLCollAtrribute with SQL_DESC_OCTET_LENGTH, it is returning me the maximum number of bytes that the column can have irrespective of whether the column in the resultset has that much data or not. Whereas, the MySQL ODBC driver is returning the maximum data that a column has for that particular resultset. Why is the anomaly? Does this information differ from driver to driver. Is there any way to know the size of a perticular row in raw-bytes before doing an SQLFetch(). I would like to allocate the memory before I do SQLFetch(). Looking into Google Groups it seems that many people have the same problem but not much solution has been provided. What is the best way to know how much bytes the RDBMS takes in the disk to store that much information? __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
