Hi,
Do you have an index on metro_name in the usa_metro_ref table ?
Jocelyn Fournier
Presence-PC
www.presence-pc.com
----- Original Message -----
From: "S A" <[EMAIL PROTECTED]>
To: "List MySQL" <[EMAIL PROTECTED]>
Sent: Saturday, May 19, 2001 4:58 AM
Subject: ORDER BY slow down, bug ?
>
> 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?
---------------------------------------------------------------------
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
