Re: query time in ~3M row table

[Brent Baisley]

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, <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]