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 > [EMAIL PROTECTED] > > 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: <[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]
