I have 3 variations of a fairly simple aggregate query done on a medium sized table of
2 million rows.
Doing an ORDER BY makes the query go from less than a second to often taking 30
seconds.
Is this a bug ?
We're running on Free BSD 4.2 with Dual PIII 700mhz & 1GB RAM.
- Sam.
A. The original query which caused the dreaded filesort.
mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a,
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >=
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id ORDER BY m.metro_name;
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+---------------------------------+
| m | ALL | PRIMARY | NULL | NULL | NULL | 108 | Using temporary; Using filesort |
| a | ref | PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metro_idx3 | 2
|| m.metro_id | 131 | where used |
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+---------------------------------+
2 rows in set (0.01 sec)
B. The next query which seemed faster but could be sometimes just as slow as the above.
mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a,
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >=
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY m.metro_name,a.metro_id ORDER
BYm.metro_name;
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+-----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+-----------------+
| m | ALL | PRIMARY | NULL | NULL | NULL | 108 | Using temporary || a | ref |
|PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metr
o_idx3 | 2 | m.metro_id | 131 | where used |
+-------+------+-------------------------------------------------+---------------------+---------+------------+------+-----------------+
2 rows in set (0.01 sec)
C. The simplest form of query with no ORDER BY at all which is never slow now.
mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a,
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >=
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id;
+-------+--------+-------------------------------------------------+---------------------+---------+------------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+-------------------------------------------------+---------------------+---------+------------+-------+-----------------------------+
| a | ref | PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metro_idx1 | 4
|| const | 10677 | where used; Using temporary |
| m | eq_ref | PRIMARY | PRIMARY | 4 | a.metro_id | 1 | where used |
+-------+--------+-------------------------------------------------+---------------------+---------+------------+-------+-----------------------------+
---------------------------------
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
