Is there an solution for the behavior of mysql, described below? I'm using mysql 3.23.41 on MacOSX... The problem is, that MySQL isn't able to use indexes for OR in the WHERE clause yet and therefore the additional OR prevents optimal use of indexes. MySQL uses only one index for a select, so it can't use an index when there is an OR in the where clause.
I tested this behavior by using the EXPLAIN SELECT ... Im my Table there are three Indexes (na, fn, na_2 = composite na + fn) (all columns are not null...) mysql> explain select id, na, vn from TB_BASIC_ENTREE where NA = 'Meier' AND VN = 'Gustav'; +-----------------+------+---------------+------+---------+-------------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------------+------+---------------+------+---------+-------------+------+------------+ | TB_BASIC_ENTREE | ref | NA,FN,NA_2 | NA_2 | 304 | const,const | 1 | where used | +-----------------+------+---------------+------+---------+-------------+------+------------+ very, very fast! mysql> explain select id, na, vn from TB_BASIC_ENTREE where NA = 'Meier' OR VN = 'Gustav'; +-----------------+------+---------------+------+---------+------+-------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------------+------+---------------+------+---------+------+-------+------------+ | TB_BASIC_ENTREE | ALL | NA,FN,NA_2 | NULL | NULL | NULL | 47522 | where used | +-----------------+------+---------------+------+---------+------+-------+------------+ very very slow!!! What can be done to force mysql to respect the indexes in a SELECT-Statement with OR? Is there already a newer mysql version that regards the indexes for selecting stuff with OR in the where-Clause? Is there an alternate workaround for that? Thanks in advance, Michael. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
