Hi, Yes, make a composite index by adding ApacheDate as the second column in the urlIndex index.
As a side note, Brent said that BETWEEN is not inclusive of the second parameter. But it IS inclusive. However, since you have a DATETIME column, there is no row with an ApacheDate of *exactly* '2003-10-01' (e.g. the time part is always there), so because of that, it may not include any rows with a month of 10. Maybe that is what Brent meant. :-) I was thinking of LIKE instead of BETWEEN: ... WHERE ApacheDate LIKE '2003-09%'; I think that's correct. Matt ----- Original Message ----- From: <[EMAIL PROTECTED]> Sent: Tuesday, November 04, 2003 5:11 PM Subject: Re: query time in ~3M row table > > 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]
