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]
