Dave schrieb:
Hi all,
I've been trying to optimize some of our queries against a large database
and come up against an index problem I haven't been able to find any
documentation on. I've cut the query down to the bare minimum, and found
the following --
explain Select iname,domain,serv,time from log where date between
'2008-05-10' and '2008-05-30';
+----+-------------+-------+-------+---------------+------+---------+------+
-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+
-------+-------------+
| 1 | SIMPLE | log | range | date | date | 4 | NULL
| 45178 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+
-------+-------------+
As you can see in the above query, it uses type "range" and the key date
is used. If I change it to -05-01 to -05-30 though it does not :
explain Select iname,domain,serv,time from log where date between
'2008-05-01' and '2008-05-30';
+----+-------------+-------+------+---------------+------+---------+------+-
-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-
-------+-------------+
| 1 | SIMPLE | log | ALL | date | NULL | NULL | NULL
| 353558 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-
-------+-------------+
As you can see the type is now "ALL" and it doesn't work.
as Ananda already wrote, it seems rows valid for '2008-05-01' to
'2008-05-30' are exceed the threshold when MySQL thinks it is faster to scan
the table instead of scan the index and than read the table
What could be the cause of this? It seems like its limited to a specific
number of rows? The rows in explain appear to be wrong...
mysql> Select count(id) from log where date between '2008-05-01' and
'2008-05-30';
+-----------+
| count(id) |
+-----------+
| 85232 |
+-----------+
1 row in set (0.97 sec)
"rows" is how many rows MySQL thinks it must examine to execute the query,
not the number of rows possible returned
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
