Thank you very much Paul. The order by NULL clause sped the query up from 1.5 minutes to 10 seconds! This is what we were looking for.
Thanks, Brad -----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 9:59 PM To: Brad Teale; '[EMAIL PROTECTED]' Subject: Re: grant by option on querys At 21:07 -0500 10/2/03, Brad Teale wrote: >Hi All, > >I asked earlier about a query being slow, possibly due to MySQL 'Using >temporary; Using filesort' when processing the query. I have done some >testing, and it appears that no matter what data set is used, MySQL always >performs a select with a 'grant by' clause using the temporary and filesort >methods. The only time I could force MySQL into not using these methods >happened when a did a goup by on a column that absolutely contained the same >information. Is this the standard behavior? Is there anyway to get around >this? Is there a MySQL variable I can tweak? Try adding ORDER BY NULL to suppress the implicit sorting that GROUP BY does in MySQL. Of course, that means your results won't be sorted. If you really want them sorted, you might try indexing modelhr, the column you're grouping by. You might try indexing it anyway, in fact. That may give you quicker grouping. > > >My example: >mysql> desc foo; >+----------+--------------+------+-----+------------+-------+ >| Field | Type | Null | Key | Default | Extra | >+----------+--------------+------+-----+------------+-------+ >| stn | char(4) | YES | MUL | NULL | | >| modelhr | int(2) | YES | | NULL | | >| f_temp | decimal(6,2) | YES | | NULL | | >| m_temp | decimal(6,2) | YES | | NULL | | >| yearmoda | date | | | 0000-00-00 | | >+----------+--------------+------+-----+------------+-------+ >5 rows in set (0.00 sec) > >mysql> select * from foo; >+------+---------+--------+--------+------------+ >| stn | modelhr | f_temp | m_temp | yearmoda | >+------+---------+--------+--------+------------+ >| KHOU | 6 | 90.00 | 89.60 | 2003-06-01 | >| KHOU | 6 | 76.00 | 71.60 | 2003-06-01 | >| KHOU | 6 | 75.00 | 73.40 | 2003-06-01 | >| KHOU | 6 | 88.00 | 87.80 | 2003-06-01 | >+------+---------+--------+--------+------------+ >4 rows in set (0.01 sec) > >mysql> explain select stn, modelhr, m_temp from foo group by modelhr; >+-------+------+---------------+------+---------+------+------+------------ - >--------------------+ >| table | type | possible_keys | key | key_len | ref | rows | Extra >| >+-------+------+---------------+------+---------+------+------+------------ - >--------------------+ >| foo | ALL | NULL | NULL | NULL | NULL | 120 | Using >temporary; Using filesort | >+-------+------+---------------+------+---------+------+------+------------ - >--------------------+ >1 row in set (0.01 sec) > >mysql> explain select stn, modelhr, m_temp from foo where stn='KHOU' and >yearmoda = '2003-06-02' group by modelhr; >+-------+------+---------------+------+---------+------+------+------------ - >---------------------------------+ >| table | type | possible_keys | key | key_len | ref | rows | Extra >| >+-------+------+---------------+------+---------+------+------+------------ - >---------------------------------+ >| foo | ALL | stn,stn_2 | NULL | NULL | NULL | 90 | Using where; >Using temporary; Using filesort | >+-------+------+---------------+------+---------+------+------+------------ - >---------------------------------+ >1 row in set (0.05 sec) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
