Hello
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.
bye,
-christian-
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 us
ed; Using index |
+------------+-------+---------------+------+---------+------+--------+---------
----------------+
1 row in set (0.00 sec)
--
Christian Hammers WESTEND GmbH - Aachen und Dueren Tel 0241/701333-0
[EMAIL PROTECTED] Internet & Security for Professionals Fax 0241/911879
WESTEND ist CISCO Systems Partner - Premium Certified
---------------------------------------------------------------------
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
