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]
