A function on the left hand side will nullify the use of an index.
Marc Dumontier wrote:
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]
