Additionally you should fix your key structure. Since BindId is already your primary key
Change the sequence of the BindId,RecordType index. Make RecordType the leftmost prefix in the compound index, you'll get a "free index" from this compound. -----Original Message----- From: Dathan Pattishall [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 12:05 PM To: Marc Dumontier; [EMAIL PROTECTED] Subject: RE: slow date query 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
