Forum Moderators: open
In MySQL when I run a query which contains WHERE, ORDER BY and LIMIT, the query is very slow (minutes to execute). The fields used in the WHERE, and ORDER BY are both indexed. When I run an EXPLAIN, it shows that it can use the index from the WHERE field, but does not mention the index from the ORDER BY field. It then does a file sort which is why it takes for ever.
I've spent 2 days trying to figure this out... any insight would be greatly appreciated!
I would give this a read and see if anything pops out at you
[dev.mysql.com...]
Under reasons that MySQL can not use an index for sorting it says:
"The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;"
Is this correct? Does this mean that if I use a WHERE and an ORDER BY, the fields used for both of these must be the same, in order to use the index for sorting? That just doesn't seem right... What am I missing?
From what I am reading here though you can check what "filesort" algorithm it is using...apparently there are two.
With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.
I would try that.... using explain check if "using filesort" shows... if yes then that is the issue.... still reading to understand what to do about it though.
The orig method reads rows twice: One time when evaluating the WHERE clause, and again after sorting the pair values. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)
If you are indeed using the original method you can see why it would be so horribly slow.
The modified filesort algorithm incorporates an optimization such that it records not only the sort key value and row position, but also the columns required for the query. This avoids reading the rows twice.
[edited by: Demaestro at 9:45 pm (utc) on Feb. 13, 2008]
* Increase the size of the sort_buffer_size variable.
*Increase the size of the read_rnd_buffer_size variable.
*Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. For example, CHAR(16) is better than CHAR(200) if values never exceed 16 characters.
*Change tmpdir to point to a dedicated filesystem with large amounts of free space. Also, this option accepts several paths that are used in round-robin fashion, so you can use this feature to spread the load across several directories. Paths should be separated by colon characters (“:”) on Unix and semicolon characters (“;”) on Windows, NetWare, and OS/2. The paths should be for directories in filesystems that are located on different physical disks, not different partitions on the same disk.
[edited by: Demaestro at 9:49 pm (utc) on Feb. 13, 2008]
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
You need a composite index consisting of key2+key1.
You can drop the index you currently have on key2, as the key2+key1 index can be used for the same purposes. That is, once you create the new index, key2 is redundant.
