Re: PHP and MySQL design question

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