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]

Reply via email to