You're saying that when you try my example, it does use the composite index? Even with an extra column in the table that isn't being searched on (q below)?
If so, do you know of anything in version 4.0.13 that could cause this bad behaviour? i'm using the default configuration unchanged. On Tue, 5 Aug 2003, Alexander Keremidarski wrote: > Joshua, > > 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]
