It's my understanding that MySQL will only use one index per table on a
given query. For example...
SELECT * FROM <HUGE_TABLE>
WHERE <col1> = <val1>
AND <col2> < <val2>
AND <col3> > <val3> ;
If col1, col2, and col3 are indexed the query can only use one index, right?
Single index access is a problem when you very large tables. What if you
have a query with a result set of just 10 rows but there are no indexed
columns that can limit the result set to < 1 million rows? I really need to
be able to use multiple indexes in a single table query and I don't want to
have to perform self joins or create temp tables.
Is there another way? Are there plans to overcome this limitation?