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'
?
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
