Multiple Indexes - Adverse effects?

[guru5571]

I'm running MySQL with MyISAM tables.

I have 5 columns which are each individually indexed.

I have a query that aliases each of these five colums so it looks like a single column.

Is it OK to also create a single index on these 5 columns, so that this particular query will run faster?

The reason I ask is because phpMyAdmin gives me this message when I add the additional index.

More than one INDEX key was created for column `event_date

Will this cause problems?

[aspdaddy]

The more indexes you have the poorer the performance is overal, both in terms of disk space used and time needed for inserts/updates.

As a general rule only index the primary keys and fields used in where clauses or sorts.

I have a query that aliases each of these five colums so it looks like a single column.

Thats sounds like concatenation not aliasing? If it is it might be better to do it in the application in using php/arrays and control the output a few records at a time

[guru5571]

Thanks for answering aspdaddy,

Here is the situation:

Each record in the DB table has 5 factors (columns) that can be measured. I essentially order the results displayed on the web page by these factors.

Sometimes I want to select only one of the factors seperately. So I have each factor (column) set as an index.

More often however, I select the results by taking all factors into consideration. For example:

SELECT

factor_1 AS a
factor_2 AS a
factor_3 AS a
factor_4 AS a
factor_5 AS a

Then perform a query using:

WHERE a = 10

Hope this clarifies a bit.
So I'm wanting to have all of these factors as a single index as well as individual indexes as I mentioned at the start . But of course I also get that message from phpMyAdmin and I'm wondering whether I can have them all included in a single index as well as each one individually indexed.

[guru5571]

It's actually quite a bit more complex and I only use the above example to illustrate that I have reasonable need to index all 5 factors as a group and also index individually, but not sure of what problems this may create, if any. That is really the gist of the question.

[jtara]

It sound to me like your database could use some normalization.

Whenever I see columns named "factor1", "factor2", etc. that raises a red flag. Your 5 factors look like a good candidate for a seperate table.

I assume you have some sort of unique ID per record. Create a seperate table, "factor". Each row is the unique ID from the main table, and a factor value.

This way, you are not limited to 5 factors, and you only need a single index.

Does the position of any factor (1,2, etc.) convey any meaning in and of itself? If so, then this may not make sense, and/or you may have to add an index value to the new table.

[aspdaddy]

You could still do it:

tblItem( id, name)
tblFactor(id,name)
tblItemFactor(itemID,factorID, position)

[guru5571]

Well you could argue that there are some normalization issues here. I agree. However, I'm really just interested in what the consequences of having each column as an index individually as well as having all 5 in a single index.