Re: KEY vs. INDEX
Hi, David T. Ashley wrote: In reading the syntax of the CREATE TABLE statement, I'm a little confused by the two keywords KEY and INDEX. What does one use when one wants MySQL to arrange the column so that it can find a given record WHERE column=whatever in approximately O(log N) time? This is a key, right? MySQL uses some kind of a BTREE arrangement, right? Yes. Most indexes are B+ trees. KEY and INDEX are synonyms in general. There may be places where it matters but I can't think of any. In any case it's just syntax, not functionality. Some storage engines offer other kinds of indexes: MyISAM offers fulltext and spatial, MEMORY allows hash indexes. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: KEY and index
Hello. On Sat, Jun 23, 2001 at 01:01:41AM +0020, [EMAIL PROTECTED] wrote: I understand that indexes work so fast because they are usually smaller than the original table, since they contain on average just 1 column, or at least less information than the complete table. But how about a table with just 2 columns. Would an index based on the same 2 columns speed up processing, more specific, would it speed up a SELECT WHERE (the WHERE clause being related to the index algoritm) Yes. The reason is, that in the table itself, the records are stored in no particular order. So, to find a row, MySQL would have to scan the whole table. An index, with MyISAM tables, stored as a tree, implicitly has an order of all values of the column(s) in question and has an additional pointer to the data file, to indicate where the belonging record is saved. There are a number of methods for searching within sorted sets, but as a simplification, have a look at binary search (http://whatis.techtarget.com/definition/0,289893,sid9_gci349425,00.html). It needs log_2(N) steps to find the locating an item, i.e. for 1,000,000,000 it needs only about 30 steps (power(2,30)=1073741824). That means, only 30 disk reads instead of 1,000,000,000. And MySQL even does better. That's the main reason why an index works fast. Bye, Benjamin. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: KEY and index
An index is not just putting the data in a separate file. It's about storing information in more high-tech data structures like B-trees, R-trees and hashes. MySQL only support B-Trees, which is the most commonly used. B-Trees are about storing data in a tree-like structure for very fast retrieval on slow media, like hard disks. The penalty is a bit slower insertion of data. But the results are extremely good when searching. In MySQL having a table that looks like this: idint primary key namechar(200) and making an index on name, will get you a 0.1 * original_retrieval_time when searching, but 1.5*original_insertion_time when inserting. It roughly means that (on my system that is) you should do at least one select statement for every 4000 inset statements, for the index to be profitable. So, keep in mind that an index wont allways suit you, but mostly. Daniel Åkerud. Quoting Cal Evans [EMAIL PROTECTED]: Regular keys are just indexes, not necessarily unique, not necessarily on fields that don't accept nulls. Their primary function is to speed up data retrieval. Use them sparingly as they can have a negative impact on inserting and updating records. I understand that indexes work so fast because they are usually smaller than the original table, since they contain on average just 1 column, or at least less information than the complete table. But how about a table with just 2 columns. Would an index based on the same 2 columns speed up processing, more specific, would it speed up a SELECT WHERE (the WHERE clause being related to the index algoritm) Thanks, Marco - Original Message - From: Marco Bleeker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 22, 2001 9:36 AM Subject: KEY and index Hello, I am learning MySQL and have a question. I know about the importance of the PRIMARY KEY statement, but I also see a KEY statement being used when creating a table. Is this just short for PRIMARY KEY, or what's the difference? Second question, when I have a large table, but with only 2 small cells per row (2 columns), is it useful to create a PRIMARY KEY, KEY, or INDEX for speed (there is no set relation to another table). Third question, when exactly does MySQL use indexes. Is it used when the indexed column is part of a (SELECT *) WHERE statement, together with a non-indexed column? I just want to check if matching a row is present or not, not actually retrieve information (WHERE ip='$ip' and datecurdate()) Thanks, Marco | | Marco Bleeker, Amsterdam | [EMAIL PROTECTED] | http://www.euronet.nl/users/mbleeker/ | | Attachments only after prior notice please. | Don't put me on any kind of mailing list. | | I am now receiving the Snowhite virus 4x a day | - some of you must be infected, please check ! | (No, you did not get it from me, I use Eudora) | __@ | _`\,_ |__(*)/ (*)Ah, op DIE fiets ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: KEY and index
Daniel Åkerud wrote: In MySQL having a table that looks like this: idint primary key namechar(200) and making an index on name, will get you a 0.1 * original_retrieval_time when searching, but 1.5*original_insertion_time when inserting. It roughly means that (on my system that is) you should do at least one select statement for every 4000 inset statements, for the index to be profitable. So, keep in mind that an index wont allways suit you, but mostly. Where did you get this numbers from, are they some general factors or do they depend on the table design? //Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: KEY and index
They most certainly do depend on the table design and especially on the system which runs the test. They are my own figures... Daniel Åkerud wrote: In MySQL having a table that looks like this: idint primary key namechar(200) and making an index on name, will get you a 0.1 * original_retrieval_time when searching, but 1.5*original_insertion_time when inserting. It roughly means that (on my system that is) you should do at least one select statement for every 4000 inset statements, for the index to be profitable. So, keep in mind that an index wont allways suit you, but mostly. Where did you get this numbers from, are they some general factors or do they depend on the table design? //Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: KEY and index
KEY is used to build other indexes. It's not short for Primary Key. There are PRIMARY KEY indexes, candidate key indexes (these COULD be a primary key but for one reason or another , are not) and just regular indexes. All tables should have a primary key. (I'm partial to adding an auto_increment field to almost every table of the name tableNameID and making this my primary key) Some tables will also have a candidate key. This is especially true if you manufacture a PK like I do. Then there may be a piece of data that is unique to each record and never null that would normally serve as a primary key. (The reason I manufacture PKs is because if a piece of data means something then it is always possible that it will change.This means that you would have to trace down all your FK relationships and change the data in those tables as well.SSN, phone number, email address are all examples of candidate keys but also smart keys. I never use them as PKs. ) Regular keys are just indexes, not necessarily unique, not necessarily on fields that don't accept nulls. Their primary function is to speed up data retrieval. Use them sparingly as they can have a negative impact on inserting and updating records. HTH, Cal * * Cal Evans * Senior Internet Dreamer * http://www.calevans.com * - Original Message - From: Marco Bleeker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 22, 2001 9:36 AM Subject: KEY and index Hello, I am learning MySQL and have a question. I know about the importance of the PRIMARY KEY statement, but I also see a KEY statement being used when creating a table. Is this just short for PRIMARY KEY, or what's the difference? Second question, when I have a large table, but with only 2 small cells per row (2 columns), is it useful to create a PRIMARY KEY, KEY, or INDEX for speed (there is no set relation to another table). Third question, when exactly does MySQL use indexes. Is it used when the indexed column is part of a (SELECT *) WHERE statement, together with a non-indexed column? I just want to check if matching a row is present or not, not actually retrieve information (WHERE ip='$ip' and datecurdate()) Thanks, Marco | | Marco Bleeker, Amsterdam | [EMAIL PROTECTED] | http://www.euronet.nl/users/mbleeker/ | | Attachments only after prior notice please. | Don't put me on any kind of mailing list. | | I am now receiving the Snowhite virus 4x a day | - some of you must be infected, please check ! | (No, you did not get it from me, I use Eudora) | __@ | _`\,_ |__(*)/ (*)Ah, op DIE fiets ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: KEY and index
The manual also states that INDEX is a synonym for KEY, which means that they have identical funcationality. Daniel Åkerud - Original Message - From: Cal Evans [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Marco Bleeker [EMAIL PROTECTED] Sent: Friday, June 22, 2001 6:27 PM Subject: Re: KEY and index KEY is used to build other indexes. It's not short for Primary Key. There are PRIMARY KEY indexes, candidate key indexes (these COULD be a primary key but for one reason or another , are not) and just regular indexes. All tables should have a primary key. (I'm partial to adding an auto_increment field to almost every table of the name tableNameID and making this my primary key) Some tables will also have a candidate key. This is especially true if you manufacture a PK like I do. Then there may be a piece of data that is unique to each record and never null that would normally serve as a primary key. (The reason I manufacture PKs is because if a piece of data means something then it is always possible that it will change.This means that you would have to trace down all your FK relationships and change the data in those tables as well.SSN, phone number, email address are all examples of candidate keys but also smart keys. I never use them as PKs. ) Regular keys are just indexes, not necessarily unique, not necessarily on fields that don't accept nulls. Their primary function is to speed up data retrieval. Use them sparingly as they can have a negative impact on inserting and updating records. HTH, Cal * * Cal Evans * Senior Internet Dreamer * http://www.calevans.com * - Original Message - From: Marco Bleeker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 22, 2001 9:36 AM Subject: KEY and index Hello, I am learning MySQL and have a question. I know about the importance of the PRIMARY KEY statement, but I also see a KEY statement being used when creating a table. Is this just short for PRIMARY KEY, or what's the difference? Second question, when I have a large table, but with only 2 small cells per row (2 columns), is it useful to create a PRIMARY KEY, KEY, or INDEX for speed (there is no set relation to another table). Third question, when exactly does MySQL use indexes. Is it used when the indexed column is part of a (SELECT *) WHERE statement, together with a non-indexed column? I just want to check if matching a row is present or not, not actually retrieve information (WHERE ip='$ip' and datecurdate()) Thanks, Marco | | Marco Bleeker, Amsterdam | [EMAIL PROTECTED] | http://www.euronet.nl/users/mbleeker/ | | Attachments only after prior notice please. | Don't put me on any kind of mailing list. | | I am now receiving the Snowhite virus 4x a day | - some of you must be infected, please check ! | (No, you did not get it from me, I use Eudora) | __@ | _`\,_ |__(*)/ (*)Ah, op DIE fiets ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
