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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
