Indexes on a Read Only Database

Question

Im not sure whether this is the place for this question but here goes:

I have a read-only database, it contains many tables which are accessed and searched using c# desktop application.

I am looking at indexes and most of the tutorials and information about indexes focus on the tradeoff between SELECT performance and INSERT / UPDATE performance with introducing indexes.

My question is, with a read only database, what would be the downside of putting indexes on every column and every combination of columns? (assuming I also dont care much about the size of the database?)

or to put it another way, can you "Over Index" a read only database?

Answer 1

Let's think about what happens when you insert / update a row in an indexed table (let's assume we're using standard B-tree indexes). The entry will be added to the table itself as well as an entry being made in each of the indexes on the table. That's what creates the time / space overhead.

To answer your question directly no, outside the initial time / space overhead of generating the indexes, there's no major downside of putting indexes on every column on every table. Remember, when you're performing a query, only up to one index can be used per table. By having lots of indexes / composite indexes, you're giving the optimiser the best choice when deciding which indexes to use.

With that said, it's messy to start generating arbitrary indexes having put little thought into. If I were you, I'd look at what queries you need to run faster and start generating indexes accordingly.

Answer 2

Actually, iirc, a warehouse-specific system, SybaseIQ does just that -- places every field in its own index. But I don't like the idea. I am extremely leery of the idea that if something is a good idea over there, it is also a good idea everywhere. I call it the Tomm Carr Universal Rule that is applicable in all circumstances under all conditions in all contexts, or TCUR for short.

Which is:

Except for the Tomm Carr Universal rule that is applicable in all circumstances under all conditions in all contexts, there is no one rule that is applicable in all circumstances under all conditions in all contexts.

Which simply means that the very best rule, standard or default we can possibly develop can never be anything more than a good start.

So if you want to design the best warehouse you can, you're going to have to put in the work. Now the fact that this is a warehouse means you can use indexes more readily than in an OLTP system. But more does not translate to "throw them around willy-nilly."

Analyze the queries. Sort them from most often used to least often used. Some are used only for reports that are generated monthly, quarterly or annually. You can pretty much forget about those -- even if you can reduce the execution time from ten minutes to ten seconds...it's probably not worth the effort.

Tune the system for the queries that execute the most often. Then work with the less often, tuning if you can without affecting the first group.

Oh, and a word, if I may, on covering indexes. Generally, we're told to look at every field the query mentions:

select  a, b, c
from    table
where   e = f
    and g > something;

Then a covering index would contain the fields a, b, c, e, f and g.

Not necessarily a good idea or, at least, not necessarily the best idea. Consider that the filtering could involve hundred, thousands or millions of records before arriving at a very small or even one single result. There is no reason to be shuffling around an index containing fields a, b and c while doing all that filtering using only e, f and g. The best design here would be two covering indexes: one with a, b, c and the other with e, f, g. Call them the results index and the filtering index. So the filtering is performed using smaller rows (more rows per I/O) and when all that work is finished, then go out to the results index to get the much fewer answer(s).

But don't forget the TCUR applies here as well. Only a good, thorough analysis can tell you which way to go.