> -----Ursprüngliche Nachricht----- > Von: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] > Gesendet: Dienstag, 13. Oktober 2009 15:26 > An: Skoric, Majk > Cc: mysql@lists.mysql.com > Betreff: Re: OR vs UNION > > Majk, all, > > > I'm no optimizer expert, but your result doesn't really surprise me. > > I'll reorder your post because that makes reasoning simpler: > > majk.sko...@eventim.de wrote: > > Hi List, > > First, your table: > > > TABLEDEF. > > | KTEMP | CREATE TABLE `KTEMP` ( > > `tid` bigint(20) NOT NULL auto_increment, > > `kunde_id` varchar(20) collate utf8_bin NOT NULL, > > `buchung_id` varchar(20) collate utf8_bin NOT NULL default '0', > > `buchungs_kunde_id` varchar(20) collate utf8_bin NOT NULL, > > `veranst_id` bigint(20) NOT NULL, > > `rolle_nummer` int(11) default '0', > > `status` tinyint(1) unsigned NOT NULL, > > `tstamp_insert` bigint(20) NOT NULL, > > `tstamp_update` bigint(20) NOT NULL, > > `KategorienWechsel` tinyint(4) NOT NULL default '0', > > PRIMARY KEY (`tid`), > > KEY `buchungs_kunde_id` > (`buchungs_kunde_id`,`veranst_id`,`status`), > > KEY `kunde_id` (`kunde_id`,`veranst_id`,`status`) > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | > > So you have two indexes which consist of three fields each, and the > least significant two fields are the same for both indexes. > > > You do a SELECT that fully specifies values for these two indexes, > combining them with "OR": > > > > > mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = > > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) or > (kunde_id= > > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1); > > +----+-------------+-------+-------------+--------------------------- > -+- > > ---------------------------+---------+------+------+----------------- > --- > > ----------------------------------+ > > | id | select_type | table | type | possible_keys > | > > key | key_len | ref | rows | Extra > > | > > +----+-------------+-------+-------------+--------------------------- > -+- > > ---------------------------+---------+------+------+----------------- > --- > > ----------------------------------+ > > | 1 | SIMPLE | KTEMP | index_merge | buchungs_kunde_id,kunde_id > | > > buchungs_kunde_id,kunde_id | 71,71 | NULL | 2 | Using > > union(buchungs_kunde_id,kunde_id); Using where | > > +----+-------------+-------+-------------+--------------------------- > -+- > > ---------------------------+---------+------+------+----------------- > --- > > ----------------------------------+ > > 1 row in set (0.00 sec) > > > > All seems fine here . Optimizer choose to use an union! This is the > same > > as following union query. > > As an alternative, you replace the "OR" by a UNION. No real change: > > > > > mysql> explain extended (SELECT * FROM KTEMP WHERE buchungs_kunde_id > = > > 'dfb49c8c0b441e9f' and veranst_id = 16058 and status = 1) UNION > (SELECT > > * FROM KTEMP WHERE kunde_id = 'dfb49c8c0b441e9f' and veranst_id = > 16058 > > and status = 1); > > +----+--------------+------------+------+-------------------+-------- > --- > > --------+---------+-------------------+------+-------------+ > > | id | select_type | table | type | possible_keys | key > > | key_len | ref | rows | Extra | > > +----+--------------+------------+------+-------------------+-------- > --- > > --------+---------+-------------------+------+-------------+ > > | 1 | PRIMARY | KTEMP | ref | buchungs_kunde_id | > > buchungs_kunde_id | 71 | const,const,const | 1 | Using where > | > > | 2 | UNION | KTEMP | ref | kunde_id | > kunde_id > > | 71 | const,const,const | 1 | Using where | > > |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL > > | NULL | NULL | NULL | | > > > > Note that both queries fully specify the index values. > > > Then, you apply Boolean logic to factor out the two identical > predicates > from both the "OR" alternatives: > > > > > But the following query is handled in a strange way > > > > mysql> explain SELECT * FROM KTEMP WHERE (buchungs_kunde_id = > > 'dfb49c8c0b441e9f' or kunde_id= 'dfb49c8c0b441e9f') and veranst_id = > > 16058 and status = 1; > > +----+-------------+-------+------+----------------------------+----- > -+- > > --------+------+---------+-------------+ > > | id | select_type | table | type | possible_keys | key > | > > key_len | ref | rows | Extra | > > +----+-------------+-------+------+----------------------------+----- > -+- > > --------+------+---------+-------------+ > > | 1 | SIMPLE | KTEMP | ALL | buchungs_kunde_id,kunde_id | NULL > | > > NULL | NULL | 1040700 | Using where | > > +----+-------------+-------+------+----------------------------+----- > -+- > > --------+------+---------+-------------+ > > > > I don't get it! Maybe someone has a clue or a hint for me. > > While that is equivalent from a logic point of view, it is different > for > the optimizer: > You do not specify the values for any of the indexes completely, > because > the first term contains an "OR" over two different fields, and the > other > terms don't specify the most significant fields of the index. > > The only way for the system to use an index would be to revert your > change and to go back to the first statement, where the "OR" is on the > outermost level.
Ah ok. I got it! > > > > > > > mysql> SELECT VERSION(); > > +---------------------+ > > | VERSION() | > > +---------------------+ > > | 5.0.27-standard-log | > > +---------------------+ > > 1 row in set (0.00 sec) > > I don't think using a newer version would change anything, but you > should be working on an update nonetheless. There were several > important > changes since that version, including security fixes. Its on my TODO list ;) > > But as 5.0 is approaching its EOL and 5.1 is GA, I hope that update > will > target 5.1. > > > HTH, > Jörg Thanks for your time! Majk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
