Joshua Spoerri wrote:
On Tue, 5 Aug 2003, Alexander Keremidarski wrote:
MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour.
is 100,000 rows small? my simple OR queries take longer than a second.
No. It is not!
I referred to your test case in your previous email:
Oddly, it works, but the following does not: mysql> create temporary table x (y int, z int, q int, index (y, z)); insert into x values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1;
mysql> create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1;
Query OK, 0 rows affected (0.00 sec)
Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0
Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0
+-------+------+---------------+------+---------+------+--------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+--------+-------------+ | x | ALL | y | NULL | NULL | NULL | 101200 | Using where | +-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
Same table:
mysql> explain select * from x where y = 1 or z = 1;
+-------+-------+---------------+------+---------+------+-------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+-------+-------------------------+
| x | index | y | y | 10 | NULL | 85971 | where used; Using index |
+-------+-------+---------------+------+---------+------+-------+-------------------------+
Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0
Best regards
-- Are you MySQL certified? -> http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria <___/ www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
