Forgive me, that example is no good. 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; Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 +-------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+-------------+ | x | ALL | y | NULL | NULL | NULL | 3 | Using where | +-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) On Mon, 4 Aug 2003, Joshua Spoerri wrote: > That doesn't seem to work (and not with bigger table either): > > mysql> create temporary table x (y int, z int, index (y), index(z)); insert into > x values (1,2), (3,4), (5,6); explain select * from x where y = 1 or z = 1; > Query OK, 0 rows affected (0.00 sec) > > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > +-------+------+---------------+------+---------+------+------+-------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +-------+------+---------------+------+---------+------+------+-------------+ > | x | ALL | y,z | NULL | NULL | NULL | 3 | Using > where | > +-------+------+---------------+------+---------+------+------+-------------+ > 1 row in set (0.00 sec) > > mysql> quit > Bye > $ rpm -qa | grep -i mysql > perl-DBD-MySQL-1.2216-4 > MySQL-devel-4.0.13-0 > MySQL-client-4.0.13-0 > MySQL-server-4.0.13-0 > MySQL-shared-compat-4.0.13-0 > > (Thanks for your help) > > On Mon, 4 Aug 2003, Alexander Keremidarski wrote: > > > Joshua, > > > > Joshua Spoerri wrote: > > > Which version is targetted for optimization of OR searching on two keys, > > > that is, "select * from sometable where f1 = 123 or f2 = 123", > > > as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html > > > ? > > > > As described there MySQL can use only one index per table. It will work same way > > untill next major Optimizer update which is scheduled for 5.1 > > > > Meanwhile your query can be optimized with using of composite index over both > > columns: > > > > mysql> explain select * from t where f1 = 10 or f2 = 10; > > +-------+-------+---------------+------+---------+------+-------+--------------------------+ > > | table | type | possible_keys | key | key_len | ref | rows | Extra > > | > > +-------+-------+---------------+------+---------+------+-------+--------------------------+ > > | t | index | f1 | f1 | 10 | NULL | 16384 | Using where; > > Using index | > > +-------+-------+---------------+------+---------+------+-------+--------------------------+ > > > > > > > > > > > Thanks > > > > > > > > > > -- > > 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]
