2

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 |
+----+-------------+------------------+------+---------------+------+---------+------+--------+-------------+
wallyqs
  • 123
  • 3

2 Answers2

3

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
UNION ALL
SELECT type FROM users WHERE type = 3;

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

gbn
  • 70,237
  • 8
  • 167
  • 244
1

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; 
SELECT COUNT(DISTINCT type) typecount 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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536