DATABASE - Table Scan vs. Add Index - which is quicker?

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.

This question and answers originated from www.stackoverflow.com
Question by (8/27/2008 1:30:20 PM)

Answer

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 by
Find More Answers
Related Topics  database  query  optimization  indexing
Related Questions