Well 1st of all Date_format doesn't allow the use of a key.
Do this. SELECT .. WHERE DateLastRevised >= '2004-12-07' AND DateLastRevisted < '2004-12-08'; -----Original Message----- From: Marc Dumontier [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 11:34 AM To: [EMAIL PROTECTED] Subject: slow date query Hi, I'd really appreciate any help in speeding up this type of query SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; On a MYISAM table of 122,000 rows, this query takes very long, in the neighbourhood of 20 minutes. i'm using mysqld 4.0.20. I have an index on DateLastRevised mysql> show indexes from BrowseData; +------------+------------+--------------------+--------------+--------- --------+-----------+-------------+----------+--------+------+---------- --+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------+------------+--------------------+--------------+--------- --------+-----------+-------------+----------+--------+------+---------- --+---------+ | BrowseData | 0 | PRIMARY | 1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_dlr | 1 | DateLastRevised | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype | 1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype | 2 | RecordType | A | 122850 | NULL | NULL | | BTREE | | +------------+------------+--------------------+--------------+--------- --------+-----------+-------------+----------+--------+------+---------- --+---------+ mysql> explain SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; +------------+------+---------------+------+---------+------+--------+-- -----------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +------------+------+---------------+------+---------+------+--------+-- -----------+ | BrowseData | ALL | NULL | NULL | NULL | NULL | 122850 | Using where | +------------+------+---------------+------+---------+------+--------+-- -----------+ 1 row in set (0.00 sec) thanks, Marc Dumontier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
