List:General Discussion« Previous MessageNext Message »
From:<jabbott Date:November 4 2003 11:11pm
Subject:Re: query time in ~3M row table
View as plain text  
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, <jabbott@stripped> 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)
> >
> 

-- 

Thread
query time in ~3M row tablejabbott4 Nov
  • Re: query time in ~3M row tableBrent Baisley4 Nov
    • Re: query time in ~3M row tablejabbott5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
        • Re: query time in ~3M row tableBrent Baisley5 Nov
          • Re: query time in ~3M row tableDan Nelson5 Nov
            • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
  • Re: query time in ~3M row tableDan Nelson5 Nov