Table Scan vs. Add Index - which is quicker?

Question

I have a table with many millions of rows. I need to find all the rows with a specific column value. That column is not in an index, so a table scan results.

But would it be quicker to add an index with the column at the head (prime key following), do the query, then drop the index?

I can't add an index permanently as the user is nominating what column they're looking for.

Answer 1

Two questions to think about:

1. How many columns could be nominated for the query?
2. Does the data change frequently? A lot of it?

If you have a small number of candidate columns, and the data doesn't change a lot, then you might want to consider adding a permanent index on any or even all candidate column.

"Blasphemy!", I hear. Most sources tell you to "never" index every column of a table, but that advised is rooted on the generic assumption that tables are modified frequently.

You will pay a price in additional storage, as well as a performance hit when the data changes.

How small is small and how much is a lot, and is the tradeoff worth it? There is no way to tell a priory because "too slow" is usually a subjective measurement.

You will have to try it, measure the size of your indexes and then the effect they have in the searches. You will have to balance the costs against the increase in satisfaction of your customers.

[Added] Oh, one more thing: temporary indexes are not only physically slower than a table scan, but they would destroy your concurrency. Re-indexing a table usually (always?) requires a full table lock, so in effect only one user search could be done at a time.

Good luck.

Answer 2

I'm no DBA, but I would guess that building the index would require scanning the table anyway.

Unless there are going to be multiple queries on that column, I would recommend not creating the index.

Best to check the explain plans/execution times for both ways, though!

Answer 3

As everyone else has said, it most certainly would not be faster to add an index than it would be to do a full scan of that column.

However, I would suggest tracking the query pattern and find out which column(s) are searched for the most, and add indexes at least for them. You may find out that 3-4 indexes speeds up 90% of your queries.