Concretely, is it worth it to add an index to a query that scans the table of a mysql database, even if that index would only be used once a month?

For example, having a users a table and then querying for all the users of a certain type.

EXPLAIN SELECT type FROM users WHERE type in (1,3);

| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
|  1 | SIMPLE      | users            | ALL  | NULL          | NULL | NULL    | NULL | 1000685 | Using where |

Here is one sure way to know if an index is worth it:

Run these queries:

SELECT type,COUNT(1) indtypecount FROM users GROUP BY type WITH ROLLUP; 

This query will give you the count of each type (indtypecount aka individual typecount) and the table count as the last row.

If indtypecount is more than 5% of the rowcount of the whole table, the MySQL Query Optimizer will ignore any index no matter how many times you run ANALYZE TABLE.

If your table is huge and the indtypecount for a type is less than 5%, an index scan is better than a full table scan anyday.

If typecount (cardinality of type) is something ridiculously small, this is all a moot point. Indexing values such as gender (M,F), day of week, active status, and marital status would produce indexes that resemble monster link lists of types that the MySQL Query Optimizer would find totally useless and, thus, space wasted.

If the running time of once-a-month queries is not a concern, indexing or not is strictly your personal choice.


An IN clause breaks down to an OR

SELECT type FROM users WHERE type = 1 OR type = 3;

OR is typically (but not always) non-SARGable. That means an index won't be used too seek data but will be scanned. This is the same (roughly) as scanning the table anyway: same number of rows.

So given there is minimal benefit, I wouldn't bother in most cases.

If you have millions of rows and the query is blocking other processes, then you can do this if you have an index. No you have 2 seeks.

SELECT type FROM users WHERE type = 1
SELECT type FROM users WHERE type = 3;

Summary: not worth it unless you have a big table and blocking issues.

  • 3
    In the example above, if the "type" field contains hundreds of options, then the IN clause and an index will work very well. I agreed through if the "type" field only contains a few values then there won't be much improvement. I have had major performance improvements on large data tables (200m+ rows) when changing a date range filter (on an indexed field) using "BETWEEN" to one that uses an "IN" clause, listing all the relevant dates. Orders of magnitude improvements in most cases.
    – Dave Rix
    Oct 20 '11 at 7:50
  • 2
    @Dave Rix: agree. I did say "typically"
    – gbn
    Oct 20 '11 at 7:54
  • :) I was just wanting to clarify really. I am working with an OR clause at the moment which is seriously /NOT/ helping, as it's an OR on it's own, combined with other ANDs, essentially causing none of the indexes to be used efficiently!!! :(
    – Dave Rix
    Oct 20 '11 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.