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. > > > 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. But as 5.0 is approaching its EOL and 5.1 is GA, I hope that update will target 5.1. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
