what is the total no. of records in the table. Index will be used , if the query selects between 5 to 10% of the total records in the table. If its more than that then, optimizer will doing ALL scan, as it assumes doing ALL scan is faster than an INDEX SCAN.
On 6/9/08, Dave <[EMAIL PROTECTED]> wrote: > > 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] > >
