List:General Discussion« Previous MessageNext Message »
From:Andy Leung Date:May 6 2000 4:22am
Subject:Order in compote index & performance [ WAS: OR, IN & index
usage]
View as plain text  
At 05:00 PM 5/5/00 +0300, sinisa@stripped wrote:
>
>Hi!
>
>Yes, index would be used, provided your table is large enough and
>expressions do not cover large part of the table.
>
>The order of elements is irrelevant, as server rearranges them anyway.

Hi,

I understand that the order of the conditions in the WHERE clause of a query
is irrelevant as the server rearranges them. But I was talking about the
order of the elements in a composite index, and surely the server would not
rearrange that order!

What I was asking was whether that order in a composite index has any effect
on performance. For example say I have a query:

SELECT *
FROM table
WHERE col_1 > "A"
  AND col_2 IN ("X", "Y", "Z")

with an index on (col_1, col_2)

Now suppose 90% of the rows have col_1 > "A" while only 5% of the rows have
col_2 IN ("X", "Y", "Z"). I don't know how exactly the index B-tree is being
searched, but I was wondering would changing the order of the index so that
the more "discriminating" column (col_2) comes first would make the query
faster. That is:

SELECT *
FROM table
WHERE col_2 IN ("X", "Y", "Z")
  AND col_1 > "A"

with an index on (col_2, col_1)

Would the above scenerio be any faster?

Thanks a lot!

Andy

>
>
>Regards,
>
>Sinisa

Thread
Order in compote index & performance [ WAS: OR, IN & indexusage]Andy Leung6 May
  • Re: Order in compote index & performance [ WAS: OR, IN & indexusage]sinisa6 May
  • Re: JOB: linux sysadmin with good mysql skills [think really "mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!Jon Stephens17 Mar
    • Re: JOB: linux sysadmin with good mysql skills [think really"mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY:£40k-£55k at least!jt@camalyn.org17 Mar
Re: JOB: linux sysadmin with good mysql skills [think really"mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY:£40k-£55k at least!John Daisley17 Mar
  • Re: JOB: linux sysadmin with good mysql skills [think really "mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!Claudio Nanni18 Mar
    • Re: JOB: linux sysadmin with good mysql skills [think really"mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY:£40k-£55k at least!jt@camalyn.org18 Mar
      • Re: JOB: linux sysadmin with good mysql skills [think really "mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!Ralf Wiegand18 Mar