Hi Karl-
I often use a unix timestamp value for my dates (an unsigned mediumint is
adequate) and index that. However, using date/time functions in the where
clause does have a significant impact on the execution times, even when the
index is used and the EXPLAIN output appears the same. See example below -
I have a table called 'warn' (that currently has ~26,000,000 rows) with an
index on an unsigned mediumint column call unixts that reflects a unix
timestamp. See the results:
select count(*) from warn where unixts between unix_timestamp('2003-07-01')
and unix_timestamp('2003-07-02');
count(*)
438146
+--------+--------+---------------+--------+---------+--------+--------+----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+--------+--------+---------------+--------+---------+--------+--------+----
----+
| warn | range | unixts | unixts | 4 | [NULL] | 327512 |
where used; Using index|
+--------+--------+---------------+--------+---------+--------+--------+----
----+
1 row in set (75.41) sec
---------------------------------
select count(*) from warn where unixts between 1057032000 and 1057118400;
count(*)
438146
+--------+--------+---------------+--------+---------+--------+--------+----
----+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+--------+--------+---------------+--------+---------+--------+--------+----
----+
| warn | range | unixts | unixts | 4 | [NULL] | 327514 |
where used; Using index|
+--------+--------+---------------+--------+---------+--------+--------+----
----+
1 row in set (2.19) sec
As you can see, same plan but a major difference in execution time (over an
order of magnitude). Moral - be cautious using functions in your where
clause - you might get surprised.
Best of luck,
Rick
-----Original Message-----
From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 12:25 PM
To: [EMAIL PROTECTED]
Subject: Date query optimization
Hi folks,
I do a considerable amount of queries based on a date, and or date range. I
have not had much luck with optimizing these queries. In some cases I use a
date field and others a datetime field.
The following query searches through 34,000 + records, while specifiying the
exact date searches through 9 records.
'ROWS: 9 SEARCHED
explain
select
a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z
ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte
dby
from submit as a inner join re_idx as b on a.submitid = b.submitid
where a.submitdate = '2003-07-01';
ROWS: 34,000 + searched
explain
select
a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z
ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte
dby
from submit as a inner join re_idx as b on a.submitid = b.submitid
where year(a.submitdate)=2003 and month(a.submitdate)=7 and
dayofmonth(a.submitdate)=1;
--and year(a.submitdate)=2003 and month(a.submitdate)=7 and
dayofmonth(a.submitdate)<15;
Notice the 2nd where statement, this is how I typically do my date queries
(and it is slow). This is because I might also be searching for a range of
dates (as in the commented out "and" clause above).
Is the to_days function faster than these date functions, or have any effect
whatsoever?
Thanks for the help.
Karl
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
