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

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