Chris wrote:
>> My point here was the if you index on (a, b), you don't need to index on
>> (b, a) if both a and b are present in your where clause. The index is
>> read from left to right -- not the where clause.
> Sure you do. Look at the OP's problem and you'll see you still do.
> To quote:
> As you can see that the user can select the columns in any arbitrary
> order and a query like: select name from benchmarks where logic =
> "AUFLIA" and status = "sat" returns result after sometime.
> I added another index like (logic, status) and the query returns
> result in blazing speed but then a query like:
> select name from benchmarks where status = "sat" and logic = "AUFLIA"
> takes more time to return the result as index were not created in that
> order.
> He has both fields included in the where and the index isn't used
> because it's defined in the opposite order.

I find the OP's results difficult to believe. There must be something
else going on besides the index. The mysql docs don't agree with this
behavior for version 3.x and up.

I also couldn't replicate this behavior in one of our tables on a 4.x
server with ~2 million rows. EXPLAIN indicated the same (a,b) index
would be used regardless of the order of the fields in the where clause.
Query times were equally fast as well.

Roberto Mansfield
Institutional Research and Application Development (IRAD)
SAS Computing

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to