Thanks, works like a charm.
Marc
Dathan Pattishall wrote:
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]
