Is there any problem with having an idex of every column?

Databases
1

Hi there, i have a database with many thousands of rows and 50 odd columns. Its quite conceivable that my users may wish to search on ANY of those 50 columns, so to increase search speeds I was wondering what cost to my system (be it in performance or resources) would there be if i created an index on each of the 50 columns.

I assume there is a downside?

ps - I have plenty of disk/memory and cpu (some would say my box was massively overspecced, but im not telling anyone about it :x :D)

2

having lotsa indexes will slow down inserts, updates, and deletes, so maybe indexing every column isn’t a good idea

if it were me, i would optimize for the 98% and if the search is on another column, the user will have to accept the slower response times

3

i would not say that it was a silly question

as you might know, mysql can use only one index at each stage of row retrieval (multiple retrievals happen only with joins and subqueries)

so as soon as your query contains two conditions in the WHERE clause, mysql has to evaluate whether any of the indexes it knows about are useful

when an index is built on only one column, then it isn’t always going to be useful, unless the query only searches on that column

take a look at your most common queries and see if one or more multi-column indexes might be useful

4

r937, Im gathering here you are not suggesting i should create Millions of indexes, as such I assume you think I have asked a silly question in the first place and that i shouldnt create an index on each column

5

any chance the user can search on more than one of those 50 columns at a time?

you know, like


 WHERE column9 = 'foo'
   AND column37 = 'bar'

if this is a possibility, then 50 indexes won’t be enough, you’ll need 50! (50 factorial, which is about 30 vigintillion, or 1,000,000 to the 10th power) to cover all possibilities

6

thanks again

7

thanks for your advice. 98% of my queries (probably more) will be against a single column, but they could genuinely be against any one of those 50 columns with no one column being necessarily more popular than another. So i guess in that case creating an index for each of them can only do more good than harm …right?

I just wanted to know whether doing so would honestly cause me performance degradation in any way? this was the crux of my original question. If it only costs me more memory then I am happy, I have a server with 64GB of RAM for a database i could quite happily run with a 2GB system… (ps i realise its not ALL about memory)

thanks again for your help with this