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