MySQL Forums :: Newbie :: Indexes: how many, how much?


Advanced Search

Indexes: how many, how much?
Posted by: Mike H ()
Date: May 06, 2010 11:58PM

I have a number of tables whose queries include between one and three AND clauses with overlap in the columns being referenced.

For instance, the queries for one of the table might look like this:

select ColumnA
from TableA
where ColumnB = 5

select ColumnA
from TableA
where ColumnB = 5
and ColumnC = 2

select ColumnA
from TableA
where ColumnB = 5
and ColumnC = 2
and ColumnD = 7


In this example, how should I construct the indexes on TableA? Is it enough to have one index on each of Columns A, B and C? Or, should I create additional indexes that span multiple columns at a time?

Additionally, does the order of the columns matter at all? For instance, ColumnA might change with a greater frequency and have a broader range of values than ColumnB.

If there already exist indexes on the individual columns, should I keep them and leave them untouched?



Edited 1 time(s). Last edit at 05/07/2010 12:01AM by Mike H.

Options: ReplyQuote


Subject Written By Posted
Indexes: how many, how much? Mike H 05/06/2010 11:58PM
Re: Indexes: how many, how much? laptop alias 05/07/2010 05:03AM
Re: Indexes: how many, how much? Rick James 05/08/2010 04:26PM
Re: Indexes: how many, how much? Mike H 05/09/2010 09:06AM


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.