Hi!
MySQL's optimizer has a slight problem. OR queries cause it to get very
confused.
Try the following to get the best performance:
Rewrite SELECT FROM table WHERE (condition1) OR (condition2);
As:
(SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE
condition2);
Hope this helps!
Regards,
Chris
On Tue, 2004-02-17 at 00:05, Andreas Pardeike wrote:
> Hi List,
>
> Can someone explain the results below? It seems that MySQL has a hard
> time choosing keys for 'or' searches. The example here is very simple
> but reflects the more complex cases where lots of rows or joins are
> used perfectly:
>
> 1) That's the table I have:
>
> artikelnummer varchar(13) not null
> wordid int(11) not null
> typ enum('interntitel', ...<15 others cut off>... ,'forlag')
>
> with keys on:
>
> wordid (Collation=A, Cardinality= 52447, Index_type=BTREE)
> typ (Collation=A, Cardinality= 5, Index_type=BTREE)
> artikelnummer (Collation=A, Cardinality= 59003, Index_type=BTREE)
>
>
> 2) It's optimized and analysed and I even tried rebuilding it from
> scratch by dumping and re-reading it:
>
> mysql> analyze table wordlist;
> +---------------+---------+----------+-----------------------------+
> | Table | Op | Msg_type | Msg_text |
> +---------------+---------+----------+-----------------------------+
> | test.wordlist | analyze | status | Table is already up to date |
> +---------------+---------+----------+-----------------------------+
>
>
> 3) Some simple query to compare to:
>
> mysql> explain select wordid from wordlist where wordid in
> (4000,5000,6000);
> id 1
> select_type SIMPLE
> table wordlist
> type range
> possible_keys wordid
> key wordid
> key_len 4
> ref NULL
> rows 3
> Extra Using where; Using index
>
>
> 4) Now add a 'or' on the same field. It's still fine:
>
> mysql> explain select wordid from wordlist where wordid in
> (4000,5000,6000) or wordid in (2000,4500,8000);
> id 1
> select_type SIMPLE
> table wordlist
> type range
> possible_keys wordid
> key wordid
> key_len 4
> ref NULL
> rows 6
> Extra Using where; Using index
>
>
> 5) Same thing on two different fields. Say good-bye to indexed
> searching:
>
> mysql> explain select wordid from wordlist where wordid in
> (4000,5000,6000) or artikelnummer = '834534857345';
> id 1
> select_type SIMPLE
> table wordlist
> type ALL
> possible_keys wordid,artikelnummer
> key NULL
> key_len NULL
> ref NULL
> rows 472026
> Extra Using where
>
>
> 6) Now for fun an 'and' on the same conditions:
>
> mysql> explain select wordid from wordlist where wordid in
> (4000,5000,6000) and artikelnummer = '834534857345';
> id 1
> select_type SIMPLE
> table wordlist
> type ref
> possible_keys wordid,artikelnummer
> key artikelnummer
> key_len 13
> ref const
> rows 1
> Extra Using where
>
>
> What's the point of indices if I cannot combine two indexed fields with
> OR ?
>
> Any help appreciated,
> Andreas Pardeike
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
