If i use my table for reads only, is it good to index every column that is used in a query?

Question

This might be daft but I was wondering...

I got a database that is used for read only, i do not do any inserts.

Is it wise to index every column used in a query? if so, should i create an index for both asc and desc?

Answer 1

MySQL currently ignores asc and desc on indexes. It accepts the syntax, but just creates an ascending index:-

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Indexes do increase insert time, but if you are doing no inserts this is not an issue. They do increase the size of the table but for most common tables that is probably not a major issue.

However MySQL will only use 1 index on a table in a query. So if you had an index on every field a query would only use one of those indexes. If you table contained (as an example) details of pay slips with 1 row per employee per month, and you had an index on the employee id and the month only one of these would be used, despite that if combined this would be unique to a record. For this you need indexes that cover more than 1 column, and all the combinations could be rather a lot of indexes.

Any large text field is probably not worth indexing with a normal index (fulltext index might be useful depending on how you query the data). For these you would either need to be checking the whole value of the field, or the value at the start of the field if using LIKE. If you query was WHERE some_field LIKE '%something%' then no index would be used.

Overall, index what will be useful and ignore the rest.

Answer 2

In almost all cases this would be a bad idea.

You need to have an understanding of what kind of queries will be run against your data. Then you can develop an optimal indexing strategy.

If there are certain columns that are used (with an operator subject to index-based optimization) in most or all of your queries, and those columns have high cardinality (many different values, well-distributed), they should be indexed.

Columns with low cardinality or poor distribution and columns that are only used in queries that also use another high-cardinality index, or not used at all for selection, should not be indexed.

Instead, you should consider making some of your indexes compound (including more than one column) such that your most critical queries can be fulfilled using only columns from the index. In my experience the easiest and most effective optimization that can be made for poorly performing queries is a covering index.

Only in the case of a covering index is the order of the index likely to be important (and only when the order of the index is the same as the order specified in the query). For non-covering indexes the order of the index is likely to be unimportant.

Creating unwanted and unneeded indexes will add some overhead to your system (relatively little if you never INSERT or UPDATE the table, but the query optimizer will still have to look at those indexes) and introduces the possibility that you'll "fool" the query optimizer with a low-cardinality index when a better one is available.