It looks like MySQL is searching on the url first. I'd be curious if
you reversed your WHERE clause order, put your ApacheDate first and see
if MySQL optimizes your query differently. I'm assuming your URL field
is rather large, thus a long string comparison is taking place even
though it's indexed. If you can force MySQL to match on the dates
first, I think it would be much faster.
I wouldn't replace the two indexes you have with one. Depending on
which field you put first, the index couldn't be used on the second
field. Your also combining two different data types in an index.
On Tuesday, November 4, 2003, at 06:11 PM, <jabbott@stripped> wrote:
>
> 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?
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
