Re: PHP and MySQL design question

robertom wrote:

Chris wrote:

Roberto Mansfield wrote:

It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes.

In some cases yes but as with anything there are exceptions :) mysql (and every other db) gets it wrong sometimes.

In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns.

Multicolumn indexes definitely have their uses. But as the OP found out, they are read left to right based on the idx definition. http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html are two documents explaining this.

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. -- Postgresql & php tutorials http://www.designmagick.com/