Re: PHP and MySQL design question

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.

Do you have any performance numbers to believe that this is not the case?

Mysql will actually only use one index per table. I was surprised to find this out but it's mentioned in http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064/ - page 64 (just looked it up to include a page ref). No idea if this is mentioned anywhere on the mysql site (doubt it). -- Postgresql & php tutorials http://www.designmagick.com/