[SOLVED] Covering index

[jaymc]

I have read a bit about using covering indexes

 

In this example select b from tbl where a=5

 

Having a covering index over a,b would mean only one read from the index, rather than a index read to get the row pointer and then table to get the data

 

Simple enough, but lets say I wanted to have

 

select b,c,d,e,f from tbl where a=5

 

Does that mean, to effectively use a covering index in that situation I would need a index covering a,b,c,d,e,f

 

If so, again simple enough but what if I have various queries that pull out different fields, for example

 

select d,e,f from tbl where a=5

select b,e,f from tbl where a=5

selecte,f from tbl where a=5

 

Is that a covering index for each of them?

 

Any more info on how to use this effectively would be great

[fenway]

You are correct... a covering index can only be used if all of the keys of the index are either in the where clause or the select list.

[jaymc]

So pretty much only useful if that table is queried with the same queries over and over..

[fenway]

So pretty much only useful if that table is queried with the same queries over and over..

It depends how the table is set up... if you always tend to pull a single column based on the value of a few others (e.g. "name" in a lookup table with two uids) then it can be quite helpful.  Otherwise, covering indexes are very useful for group by / order by operations, since it can often prevent a filesort.

[jaymc]

Great, thanks