Re: DateTime Select optimised

Sun, 20 Mar 2005 21:16:53 -0800

LIKE is a string comparison. Using "date LIKE '2005-01-07%'" forces mysql to convert each datetime value in column date into a string in order to make the comparison. When your comparison is based on a function of a column (the implicitly called "cast as string", in this case), the index on the column cannot be used. Hence, you get a full table scan. Neither crippling the index on the datetime column nor adding FORCE INDEX will solve that basic problem.

Michael

Logan, David (SST - Adelaide) wrote:

Hi Paul,

On my installation I get 

mysql> explain select * from mytest where date_thing like
"2005-03-21%"\G
*************************** 1. row ***************************
        table: mytest
         type: ALL
possible_keys: date_thing
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 12
        Extra: Using where
1 row in set (0.01 sec)

mysql>

It won't use the index but you could use the FORCE INDEX (date_thing) to
ensure it does. It might be worth trying some timings to see what you
get.

BTW the FORCE INDEX is only there as of 4.0.9, if that can't be used
then try USE INDEX.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-----Original Message-----
From: Pete Moran [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 11:22 AM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

Will try with the partial index,

Comparing the two without it is as follows

Using Like

mysql> explain select count(*) from trip where pick_up_date like
'2005-01-01%';
+-------+-------+---------------+--------------+---------+------+-------
--+-
-------------------------+
| table | type  | possible_keys | key          | key_len | ref  |
rows    | Extra                    |
+-------+-------+---------------+--------------+---------+------+-------
--+-
-------------------------+
| trip  | index | pick_up_date  | pick_up_date |       8 | NULL |
9365778 | Using where; Using index |
+-------+-------+---------------+--------------+---------+------+-------
--+-
-------------------------+
1 row in set (0.00 sec)

Using 2 dates

mysql> explain select count(*) from trip where pick_up_date >
'2005-01-01' and pick_up_date < DATE_ADD('2005-01-01', INTERVAL 24
HOUR);
+-------+-------+---------------+--------------+---------+------+-------
+---
-----------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+--------------+---------+------+-------
+---
-----------------------+
| trip | range | pick_up_date | pick_up_date | 8 | NULL | 15437
| Using where; Using index |
+-------+-------+---------------+--------------+---------+------+-------
+---
-----------------------+
1 row in set (0.01 sec)

-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 11:43 AM
To: Pete Moran
Cc: mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

I would investigate a partial index perhaps on the date only? You could
index on just the date eg.

ALTER TABLE <thing> ADD INDEX (date(10));

I don't have to time to check it out now and I'm not sure it will
automatically use it but an EXPLAIN on the statement would point you in
the right direction.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-----Original Message-----
From: Pete Moran [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 11:07 AM
To: Logan, David (SST - Adelaide); mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

The table is indexed on the date field, doing a 'like' results in a table
scan, is there another way similar principal but would allow the indexes
to be used ?

-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 11:24 AM
To: Pete Moran; mysql@lists.mysql.com
Subject: RE: DateTime Select optimised

SELECT * FROM <table> WHERE date LIKE '2005-01-07%';

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-----Original Message-----
From: Pete Moran [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 10:46 AM
To: mysql@lists.mysql.com
Subject: DateTime Select optimised

Hi All,

Is there a simpler way of doing a select for a given date, for instance
if I have a datetime field called date

And so its populated with a load of values such as 

2005-01-07 09:00
2005-01-07 10:00
2005-01-07 11:00
2005-01-07 12:00

If I wanted all records which fall on 2005-01-07 I could of course do 

Select * from <TABLE> where date > '2005-01-07' and date <
DATE_ADD('2005-01-07', INTERVAL 24 HOUR)

However is there a simpler way of doing it by just passing one date like

Select * from <TABLE> where date = '2005-01-07'

? 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to