MySQL performance: sorting is slow on a large table, although the filtered subset is small
I'm trying to customize my MySQL query, but I have a problem that I don't understand (and therefore cannot fix). As such, it can sort 165,000 rows faster if they are in their own table than if they are a subset of a larger table.
Fl6 has 2 million rows. It has the index x1 on (departure_out). departure_out - date type.
The next selection finds 165,916 rows. It will take 0.1 seconds.
select count(*)
from fl6
where departure_out > "2013-04-01"
and departure_out < "2013-04-05";
The next selection has the same where clause, but sorts by price. It will take 0.5 seconds. 0.4 seconds to sort 165,000 rows.
select id
from fl6
where departure_out > "2013-04-01"
and departure_out < "2013-04-05"
order by price_total limit 1;
I wanted to see if this could be faster, so I created a small table with only 165,916 rows. Then I did it about this. It took 0.16 seconds.
select id
from fl6_small
order by price_total limit 1;
So it can sort 165,000 rows pretty quickly, but it takes more than two times if it's a subset of a larger table? How can i do this? Why the difference?
A couple of things: I've already tried putting the index (price) and (departure_out, price). It does not matter. It doesn't have to be with an index anyway, if a search in fl6_small shows how fast it can sort even without it.
EDIT:
(Edited a number of lines and times above to match the tables used for the explain plan)
Explanation plan:
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | fl6 | range | x1 | x1 | 3 | NULL | 160493 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
source to share
The difference is that in the first case, MySQL will create a temporary table with 165,000 rows and sort them without an index. Even if there is an index in the price column, it cannot be used for sorting.
Your small table can use an index for sorting and is therefore much faster.
source to share
You can use the EXPLAIN statement to check the bottlenecks of your query http://dev.mysql.com/doc/refman/5.0/en/explain.html and also http://dev.mysql.com/doc/refman/5.0/ en / optimization.html
source to share
This can be associated with several problems.
Nitpicky point - In a large table, you do more than just sort, you first look for records and then sort. On a small table, you weren't looking for depature_time. But this is a tiny trick, as a large table, in order to know which n to use, you first need to build the table. If it's indexed, it's hardly as important as your test shows. It still takes 0.1 out of 0.5 seconds. Also try the where clause on a small table, it takes a little more time. If this is not the case, it indicates:
Second, cache misses : *
2000K records can be significantly larger than 150K records on the computer you are using unless you have a dedicated mysql-only field that increases the chance of cache misses. These misses can be much more serious than if you had type n ^ 2 or worse records loaded into memory and aligned memory. If you run these tests on a box with a lot of bar, assuming everything else is the same, there should be fewer discrepancies.
source to share