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. 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-10' and '2008-05-30'; +-----------+ | count(id) | +-----------+ | 45983 | +-----------+ 1 row in set (0.52 sec) 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) Does anyone know what could be the cause of this or where to look next? Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
