MySQL Forums :: Performance :: Multiple indexes - how do they work?


Advanced Search

Multiple indexes - how do they work?
Posted by: Rathi Rao ()
Date: August 25, 2016 03:51AM

When I create mutiple indexes on a table and all those indexed columns are referred in the 'where' clause, how do they all work together to find a single row that satisfies all the conditions in the 'where' clause ?

Example -

select col1,col2,col3 from tab where col1 = xxx and col2 = yyy

index on col1, idxcol1 and index on col2,idxcol2 .. col3 being the primary key,idxcol3

when I execute this query, how do the indexes work? Does it go this way,

Step1 - idxcol1 is used to figure out the value 'xxx' and then idxcol3(primary index/key) is used to get to the row
Step2 - And then idxcol2 is used to figure out the value 'yyy' and then and the idxcol3(primary index/key) is used to get to the row

Regards
Rathi

Options: ReplyQuote


Subject Views Written By Posted
Multiple indexes - how do they work? 470 Rathi Rao 08/25/2016 03:51AM
Re: Multiple indexes - how do they work? 283 Peter Brawley 08/25/2016 11:29AM
Re: Multiple indexes - how do they work? 259 Rathi Rao 08/29/2016 03:48AM
Re: Multiple indexes - how do they work? 262 Rick James 08/26/2016 02:20PM
Re: Multiple indexes - how do they work? 250 Rathi Rao 08/29/2016 03:46AM
Re: Multiple indexes - how do they work? 301 Øystein Grøvlen 08/29/2016 01:23AM
Re: Multiple indexes - how do they work? 253 Rathi Rao 08/29/2016 03:45AM
Re: Multiple indexes - how do they work? 247 Øystein Grøvlen 08/29/2016 04:17AM
Re: Multiple indexes - how do they work? 240 Rathi Rao 08/29/2016 04:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.