Yes, it is an indexed field:
mysql> explain select count(*) from hitstats where year(apacheDate) = 2003 and
-> month(apacheDate) = 9;
+----+-------------+----------+-------+---------------+-----------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+---------+--------------------------+
| 1 | SIMPLE | hitstats | index | NULL | dateIndex | 8 | NULL |
2749862 | Using where; Using index |
+----+-------------+----------+-------+---------------+-----------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
Using between is much faster(?!) Still though, the query is slow when I add antoher
part in, such as:
mysql> select count(*) from hitstats where url like '/water/index.html' AND ApacheDate
between '2003-09-01' and '2003-10-01';
+----------+
| count(*) |
+----------+
| 2396 |
+----------+
1 row in set (14.68 sec)
mysql> explain select count(*) from hitstats where url like '/water/index.html' AND
ApacheDate between '2003-09-01' and '2003-10-01';
+----+-------------+----------+-------+--------------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+----------+-------+--------------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | hitstats | range | urlIndex,dateIndex | urlIndex | 255 | NULL
| 5368 | Using where |
+----+-------------+----------+-------+--------------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
I have two indexes, would it be better if I did the two fields in one index?
--ja
On Tue, 4 Nov 2003, Brent Baisley wrote:
> You're searching on a calculation so I'm pretty sure that MySQL is not
> using an index for the search. You should use explain in front of your
> query to see if MySQL is using indexes. You do have that date field
> indexed, don't you?
> You should search on ApacheDate between 9/1/2003 and 10/1/2003.
>
> Something like this:
> select count(*) from hitstats where apacheData between '2003-09-01' and
> '2003-10-01';
>
> I think that's right. The 'between' is not inclusive of the second
> parameter.
>
> On Tuesday, November 4, 2003, at 05:09 PM, <[EMAIL PROTECTED]> wrote:
>
> > When I try to do any sort of query the times are really long. Such as:
> >
> > mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
> > month(apacheDate) = 9; +----------+
> > | count(*) |
> > +----------+
> > | 988759 |
> > +----------+
> > 1 row in set (25.17 sec)
> >
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
