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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
