List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 6 2001 5:41pm
Subject:Re: Question regarding query optimising with between
View as plain text  
In the last episode (Mar 06), Christian Hammers said:
> I have a table with a normal multiple key on a datetime field called
> "end". I want to search for the number of records in some months. I
> know that there are none.
> 
> Now "explain" tells me that a querey with a WHERE clause of
> 	end between '2001-01-01' and '2001-04-01'
> affects just one row whereas
> 	end > '2001-01-01'
> affects all 705949 rows! Why this? I cannot find the docs that
> explain this speed enhancement. I'd even thought that a ">" would be
> faster as it is just one check and the parser could say "every row"
> starting from index position X.

The 'rows' column in the EXPLAIN output is just a guess based on the
last results of ANALYZE TABLE.  If you've added records, the guess
could be off.  It doesn't really matter anyway, since the select type
is 'range; using index' for both queries, so they will be executed the
same way by mysql.

> Full explain results:
> mysql> explain  select count(*) from radius0800 where ende between "2001-01-01"
> and "2001-04-01";
>
> +------------+-------+---------------+------+---------+------+------+-------------------------+
> | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra          
>         |
>
> +------------+-------+---------------+------+---------+------+------+-------------------------+
> | radius0800 | range | ende          | ende |       4 | NULL |    1 | where used;
> Using index |
>
> +------------+-------+---------------+------+---------+------+------+-------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> explain  select count(*) from radius0800 where ende > "2001-01-01";
>
> +------------+-------+---------------+------+---------+------+--------+-------------------------+
> | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra        
>           |
>
> +------------+-------+---------------+------+---------+------+--------+-------------------------+
> | radius0800 | range | ende          | ende |       4 | NULL | 705949 | where used;
> Using index |
>
> +------------+-------+---------------+------+---------+------+--------+-------------------------+
> 1 row in set (0.00 sec)

-- 
	Dan Nelson
	dnelson@stripped
Thread
Question regarding query optimising with betweenChristian Hammers6 Mar
  • Re: Question regarding query optimising with betweenDan Nelson6 Mar