Majk.Skoriceventim.de
Date: Tue Oct 13 2009 - 05:48:04 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]