Multiple indexes containing the same field - ok? not?

bunner_bob

What is the correct way to index a table when you have several different queries that use different fields, but there is some overlap among them?

For example, you have

SELECT * WHERE field1 = 'a' AND field2 = 'b'

and elsewhere

SELECT * WHERE field2='c' AND field3='d'

and elsewhere

SELECT * WHERE field1='e' AND field3='f'

Should I do one index on all three fields, or a separate index on each individual field, or three indexes, each containing one of the pairs of queried fields?

phpMyAdmin complains ("More than one INDEX key was created for column field1") when I have multiple indexes that include the same field (though it only seems to do so when that field is the first field in the index). Not sure whether this is really an issue or pMA being overprotective.

NogDog

To the best of my knowledge (I am by no means a DB "expert"), you should just assign indexes individually, unless you are defining a "unique" type index for two or more columns (i.e., you are specifying that the table cannot contain rows with the same exact combination of values for that combination of columns).

Sxooter

It really depends on the correlation and how you'll be doing things like order by.

In a database that supports bitmap index ops (PostgreSQL does, MySQL doesn't, unless it's in the newer alpha test code) you don't need multi-column indexes for where clauses. I.e.

select * From table where id1=2 and id2=4

could use an index on id1, and an index on id2 and then much them together with a bitmap op.

Without bitmap indexes, what normally happens is the db picks the most selective index and uses it, then uses a sequentual scan on the result set to get the rows that match the second field.

i.e. if id1 had 1000 entries for 2, and 1000 entries for 3, and so on, and id2 averaged like 5 entries per value, then the db would pick the index on id2.

bunner_bob

I'm using MySQL.

According to the manual it looks like multicolumn indexes ARE a good thing, though apparently the first column needs to be one where you're querying on a known value. So if your query is:

SELECT * FROM table1, table2 where table1.field1=2 and table1.field2=table2.field1

then a multicolumn index on field1, field 2 (in that order) would be good. But if you swapped the columns then that index wouldn't be used.

But this brings me back to the original point - phpMyAdmin complaining when I have several indexes that all include the same field (and when that field is the first field in the index) - AND re. a previous post I made, phpMyAdmin complaining about including the primary key as one of the columns in a multicolumn index.

Perhaps I should visit the pMA forum. I will try that.

bunner_bob

OK - found a post on a Google group that I think answers it. This guy believes it's fine to create multiple multi-column indexes that have some overlap, and his explanation makes sense to me:

http://groups.google.com/group/mailing.database.mysql/browse_thread/thread/cb1dc575b9707b97

Sxooter

bunner bob wrote:
I'm using MySQL.

According to the manual it looks like multicolumn indexes ARE a good thing, though apparently the first column needs to be one where you're querying on a known value.

Oh, sorry, yeah, I didn't mean to say you couldn't use multicolumn indexes. They can be quite useful.

Keep in mind though that the size of multicolumn indexes grow at the rate of the product of the size of each column you add.