explain select * from pageview where datestart='2008-09-26' and dateend='2008-09-27';
+----+-------------+----------+------+-------------------------------+-----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------------------+-----------+---------+-------------+------+-------+
| 1 | SIMPLE | pageview | ref | combo,datstrt,sped2,daterange | daterange | 6 | const,const | 2541 | |
+----+-------------+----------+------+-------------------------------+-----------+---------+-------------+------+-------+
As compared to:
explain select * from pageview where datestart>='2008-09-26 00:00:00' and dateend<='2008-09-27 00:00:00';
+----+-------------+----------+------+-------------------------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | pageview | ALL | combo,datstrt,sped2,daterange | NULL | NULL | NULL | 3315341 | Using where |
+----+-------------+----------+------+-------------------------------+------+---------+------+---------+-------------+
as you can see, the first version (date only, no time) is far more tuned than the second one. dateStart and dateEnd are both MySQL DATE fields. I have set the prototype of the fields to be Date, but I am still passing in NSTimestamps (as requested by all functions). In the MySQL slow query log, I see the following:
# Time: 080927 20:26:17
# User@Host: realtor[realtor] @ localhost [127.0.0.1]
# Query_time: 6 Lock_time: 0 Rows_sent: 14 Rows_examined: 3316728
SELECT DISTINCT RTRIM(t0.domainName), t0.id FROM PageDomain t0, PageView T1 WHERE (T1.dateEnd <= '2008-09-27 00:00:00' AND T1.dateStart >= '2008-09-26 00:00:00') AND t0.id = T1.pagedomainid;
The question is, how do I get WO/Wonder to deal with this column as a DATE, and not a timestamp?