Re: PHP and MySQL design question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux