Hi List, i have a problem with an OR STATEMENT. Maybe someone can explain to me why the mysql optimizer doesn't work like expected. Please have a look at following "similar" queries.
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. 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 | | 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. 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 | mysql> SELECT VERSION(); +---------------------+ | VERSION() | +---------------------+ | 5.0.27-standard-log | +---------------------+ 1 row in set (0.00 sec) Regards, Majk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
