RE: Date query optimization

2003-07-02 Thread Rick Robinson 
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]

Re: Date query optimization

2003-07-01 Thread Keith C. Ivey 
On 1 Jul 2003 at 9:25, Karl J. Stubsjoen wrote:

 ROWS: 34,000 + searched
 explain
 select
 a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.sta
 te,a.z
 ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.su
 bmitte 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).

The normal way to do a search for a range of dates would be 

   ... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14';

Is that what you're looking for?

-- 
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org


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

Re: Date query optimization

2003-07-01 Thread Karl J. Stubsjoen 
Keith,

 The normal way to do a search for a range of dates would be

... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14';

 Is that what you're looking for?

submitdate happens to be a DATETIME field.
Your suggested query doesn't pull any results.  Must I convert the field
first?  But then is my query unoptimized?  Should I consider storing date
parts into my database; seperate column for year - month - date and (time
parts..)

Karl


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

Re: Date query optimization

2003-07-01 Thread Keith C. Ivey 
On 1 Jul 2003 at 10:28, Karl J. Stubsjoen wrote:

  The normal way to do a search for a range of dates would be
 
 ... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14';
 
  Is that what you're looking for?
 
 submitdate happens to be a DATETIME field.
 Your suggested query doesn't pull any results.

If it's a DATETIME field, you'll want something like 

   ... WHERE a.submitdate BETWEEN '2003-07-01 00:00:00'
   AND '2003-07-14 23:59:59';

or

   ... WHERE a.submitdate = '2003-07-01 00:00:00'
   AND a.submitdate  '2003-07-15 00:00:00';

-- 
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org


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