> -----Ursprüngliche Nachricht-----
> Von: Joerg.Bruehe@stripped [mailto:Joerg.Bruehe@stripped]
> Gesendet: Dienstag, 13. Oktober 2009 15:26
> An: Skoric, Majk
> Cc: mysql@stripped
> 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.Skoric@stripped 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
