List:General Discussion« Previous MessageNext Message »
From:Matt W Date:November 5 2003 12:31am
Subject:Re: query time in ~3M row table
View as plain text  
Hi Sean,

LIKE is a range. ;-) I just tried it (on 4.0.16) with an index on (text,
num) and it uses the full key_len, so LIKE is not a problem:

EXPLAIN SELECT * FROM table WHERE text LIKE 'a%' AND num=123;

For the original poster, index on (url, ApacheDate) should be better
than (ApacheDate, url) because with his example queries, EXPLAIN
indicated that less rows would be examined when using the url index.

Oh wait, nevermind! He didn't actually post a query when the index on
ApacheDate was used (just the index scan), so I don't know for sure.
However, it's true that when he included url and ApacheDate in the
WHERE, it chose the url index, which tells me that it's more restrictive
than ApacheDate. In which case, it should still be better to composite
index (url, ApacheDate). :-)

Try it both ways to be sure and see what's faster.


Matt


----- Original Message -----
From: "sean peters"
Sent: Tuesday, November 04, 2003 5:44 PM
Subject: Re: query time in ~3M row table


> If you make the composite index (urlIndex, ApacheDate) - then the
WHERE
> condition for urlIndex cannot be a LIKE condition, it must be an exact
> condition (range should be ok too) otherwise the composite index
cannot be
> used. This is because the composite index is effectively an index on a
> concatenation of the columns in question.
>
> If you want to be able to search on either column, as well as on both,
i'd
> recommend an index on the urlIndex column, and a composite index
(ApacheDate,
> urlIndex) - because a date column is generally search for an exact or
range
> of values, and the urlIndex appears to get searched on as a begins.
>
> regards,
> sean peters
> speters@stripped
>
> On Tuesday 04 November 2003 17:29, Matt W wrote:
> > 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