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?
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) Thanks, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
