Re: Index not used for order by when more than 1 field selected, no where clause
On Fri, Feb 16, 2001 at 10:56:57PM +0100, Benjamin Pflugmann wrote: When you set up your database server so that it has enough RAM to keep the whole database in main memory (buffer cache), random reads don't need to wait for physical disk seeks and can be very fast. [...] So my question is: Is it a way to tune this threshold ? AFAIK, currently the only way is to change it within the source and recompile MySQL. It shouldn't be hard to make that a tunable parameter via /etc/my.cnf. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: Index not used for order by when more than 1 field selected, no where clause
Benjamin Pflugmann wrote : Hi. [] If MySQL uses the index, it has one of two possibilities. First: 1. Read the whole isbn index, and for each row 2. seek the record in the data file and get author from it. This is quite slow because of the random file seeks needed. Second: 1. Read the whole isbn index (since you have no restricting WHERE clause) 2. Read isbn,author from the whole data file in unsorted order (you need isbn, too, else MySQL wouldn't know where the authors belong too) 3. Sort isbn,autor (using sorted isbn, so using selection sort?) This is still slower than MySQL's approch: Third: 1. Read isbn,author from the whole data file in unsorted order 2. Sort isbn,autor as "Second" additionally has to read the index file from disk with no additional benefit. I don't know how to express that elegant, but the problem is that by including author to the queried fields, you have an unsorted column which requires a full sort. "Second" is never the best way. MySQL uses "First" up to some percentage of the amount of records (30% I believe), then uses "Third", because reading the data in file order (unsorted) and sorting is usually faster than random seeks (due to index usage) over that percentage of records. Hi all, Sorry to jump in the middle of this thread but your comment make me think of a case where random seeks may still be better even if more than 30% of records are involved: When you set up your database server so that it has enough RAM to keep the whole database in main memory (buffer cache), random reads don't need to wait for physical disk seeks and can be very fast. This is actually the case of our database server (2Gb RAM, 500 Mb database); vmstat shows that MySQL can run for hours without any disk read even under high load (more than 300 queries/s). So my question is: Is it a way to tune this threshold ? (In the case described above, it may still be faster to use random seeks up to 60 a 70% instead of 30%). Bye, Thanks -- Joseph Bueno NetClub/Trader.com - 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: Index not used for order by when more than 1 field selected, no where clause
Hi. On Fri, Feb 16, 2001 at 09:42:02AM +0100, [EMAIL PROTECTED] wrote: Benjamin Pflugmann wrote : [...] "Second" is never the best way. MySQL uses "First" up to some percentage of the amount of records (30% I believe), then uses "Third", because reading the data in file order (unsorted) and sorting is usually faster than random seeks (due to index usage) over that percentage of records. [...] Sorry to jump in the middle of this thread but your comment make me think of a case where random seeks may still be better even if more than 30% of records are involved: You are completely right. That's why I said "usually" above. But it shouldn't make a too big difference generally. In the original problem, the speed difference was so huge, because MySQL could avoid reading the data file at all in one case. When you set up your database server so that it has enough RAM to keep the whole database in main memory (buffer cache), random reads don't need to wait for physical disk seeks and can be very fast. [...] So my question is: Is it a way to tune this threshold ? AFAIK, currently the only way is to change it within the source and recompile MySQL. 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: Index not used for order by when more than 1 field selected, no where clause
Hi, I think you are mis-understanding the information provided. What the "using index" note means is that the isbn column is part of the index, and since that is all that is selected, the index file is used to provide the data, the data file is not touched. Since there is (usually) less data in the index file, and it is more organised, the query takes less time. Adding the author to the query means that the query now has to go to the data file to get the information, and since there is no restriction on isbn, the entire table is scanned for records to provide the result, which is then sorted. If you had an index of (isbn, author), then the index file would still be used. The clue is in the 'possible keys' value - NULL means that no keys are available that would help in providing the query. Can someone else tell me and Francois, when the index is used like this, is the result pre-sorted, so the order by is trivial, or is a sort still performed. Hope this helps Regards Quentin -Original Message- From: Franois Blanger [mailto:[EMAIL PROTECTED]] Sent: Friday, 16 February 2001 08:01 To: [EMAIL PROTECTED] Subject: Index not used for order by when more than 1 field selected, no where clause Hi, I'm having a hard time figuring why mySQL does not use the index when sorting a table without any where clause when I include more than 1 field in the select part. To resume the case, I created a temp table with 3 single fields, added an index (see bottom for more details), ran 2 queries with explain: This one is fine, uses the index: mysql explain select isbn from tmp order by isbn\G *** 1. row *** table: tmp type: index possible_keys: NULL key: isbn key_len: 13 ref: NULL rows: 1000 Extra: Using index 1 row in set (0.00 sec) Adding just an extra field to the select and no longuer is the index used: mysql explain select isbn,author from tmp order by isbn\G *** 1. row *** table: tmp type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using filesort 1 row in set (0.00 sec) When used with the real table which has 160k rows and is called within a complex join that works fine (0.04 sec) and uses index until I add an order by clause, then takes 12secs. Help or hints on fixing the above will probably fix my real problem. Thanks for any help. Further details below, including mySQL version (3.23.30) and OS (RedHat 6.0). Francois mysql CREATE TEMPORARY TABLE tmp select isbn,author,title from ws_inventory where available = 1 limit 1000; Query OK, 1000 rows affected (0.04 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql alter table tmp add index(isbn); Query OK, 1000 rows affected (0.09 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql explain tmp\G *** 1. row *** Field: isbn Type: char(13) Null: Key: MUL Default: Extra: Privileges: select,insert,update,references *** 2. row *** Field: author Type: char(60) Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references *** 3. row *** Field: title Type: char(255) Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references 3 rows in set (0.00 sec) mysql explain tmp; ++---+--+-+-+---+--- --+ | Field | Type | Null | Key | Default | Extra | Privileges | ++---+--+-+-+---+--- --+ | isbn | char(13) | | MUL | | | select,insert,update,references | | author | char(60) | YES | | NULL| | select,insert,update,references | | title | char(255) | YES | | NULL| | select,insert,update,references | ++---+--+-+-+---+--- --+ 3 rows in set (0.00 sec) mysql Ver 11.10 Distrib 3.23.30-gamma, for pc-linux-gnu (i686) Connection id: 157 Current database: schoen Current user: schoen_root@localhost Current pager: stdout Using outfile: '' Server version: 3.23.30-gamma Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 1 day 21 hours 24 min 47 sec Threads: 1 Questions: 655 Slow queries: 38 Opens: 89 Flush tables: 2 Open tables: 23 Queries per second avg: 0.004 - Before posting, please check: http://www.mysql.com/manual.php (the
Re: Index not used for order by when more than 1 field selected, no where clause
15/02/01 15:14, Quentin Bennett, [EMAIL PROTECTED]: Hi, I think you are mis-understanding the information provided. What the "using index" note means is that the isbn column is part of the index, and since that is all that is selected, the index file is used to provide the data, the data file is not touched. You're right. From Explain doc: Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This can be done when all the used columns for the table are part of the same index. Adding the author to the query means that the query now has to go to the data file to get the information, and since there is no restriction on isbn, the entire table is scanned for records to provide the result, which is then sorted. If you had an index of (isbn, author), then the index file would still be used. That's where I don't get it, I don't want to sort by author, only by isbn. Why does mysql needs to resort to a filesort while it has all the ISBN neatly ordered in an index? Can someone else tell me and Francois, when the index is used like this, is the result pre-sorted, so the order by is trivial, or is a sort still performed. Yes, how can we get mySQL to use an index for doing an order by? Must I create an index for each and every possible fields combination? Thanks for your lights on Using Index Quentin. Franois Blanger Sitepak: nouvelle vision Internet pour l'entreprise http://www.sitepak.com - 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: Index not used for order by when more than 1 field selected, no where clause
Hi. On Thu, Feb 15, 2001 at 03:53:39PM -0500, [EMAIL PROTECTED] wrote: 15/02/01 15:14, Quentin Bennett, [EMAIL PROTECTED]: [...] Adding the author to the query means that the query now has to go to the data file to get the information, and since there is no restriction on isbn, the entire table is scanned for records to provide the result, which is then sorted. If you had an index of (isbn, author), then the index file would still be used. That's where I don't get it, I don't want to sort by author, only by isbn. Why does mysql needs to resort to a filesort while it has all the ISBN neatly ordered in an index? You have to look at it the other way around: If MySQL uses the index, it has one of two possibilities. First: 1. Read the whole isbn index, and for each row 2. seek the record in the data file and get author from it. This is quite slow because of the random file seeks needed. Second: 1. Read the whole isbn index (since you have no restricting WHERE clause) 2. Read isbn,author from the whole data file in unsorted order (you need isbn, too, else MySQL wouldn't know where the authors belong too) 3. Sort isbn,autor (using sorted isbn, so using selection sort?) This is still slower than MySQL's approch: Third: 1. Read isbn,author from the whole data file in unsorted order 2. Sort isbn,autor as "Second" additionally has to read the index file from disk with no additional benefit. I don't know how to express that elegant, but the problem is that by including author to the queried fields, you have an unsorted column which requires a full sort. "Second" is never the best way. MySQL uses "First" up to some percentage of the amount of records (30% I believe), then uses "Third", because reading the data in file order (unsorted) and sorting is usually faster than random seeks (due to index usage) over that percentage of records. Can someone else tell me and Francois, when the index is used like this, is the result pre-sorted, so the order by is trivial, or is a sort still performed. If the index is used, the data is read in-order and therefore the sort is skipped, AFAIK. Yes, how can we get mySQL to use an index for doing an order by? Must I create an index for each and every possible fields combination? There are several reasonable approaches and which to use depends on your environment and usage. One is almost the one you propose, but not indexes for all combinations, but only for the common ones (you are restricted in the number of indexes). Since the time difference is quite large, your data file seems to be quite big and you may want to consider to split it into two tables, if the scanned fields (like author,isbn) are relatively small compared to the rest of the record. One table would contain all small fields that are needed in the full table scans and the other table the rest. On the other hand, you may question yourself whether these full tables scans are really needed that much (it sounds rather unusual). 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
