A search for 'indexes' brings this as the first result:
5.4.3 How MySQL Uses Indexes
http://www.mysql.com/doc/M/y/MySQL_indexes.html
Inside the page:
"Any index that doesn't span all AND levels in the WHERE clause is not
used to optimise the query. In other words: To be able to use an index,
a prefix of the index must be used in every AND group."
[Note: optimize is spelled wrong in the manual.]
The corollary to the above quote is that MySQL can not use indexes with
an OR clause at the base level.
It would be a worthy optimization to make. MySQL could order the items
in the OR clause of the query, skipping ahead when one section is
complete and on to the next (assuming a single index would be used). Or
it could internally create a temp table and break the WHERE clause into
several smaller queries that do use indexes. In fact, you can do that
yourself as well (either using temp tables or UNIONs in version 4).
Makes for messy SQL writing to work around this optimization issue, so
I'm sure someone will add it to MySQL some day. Maybe it will be you! :)
Sincerely,
Steven Roussey
http://Network54.com/?pp=e
