MySQL - Small table popping up in slowlog, refuses to use index

Question

So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes).

I tested using a large multipart index (that's just all the columns being selected) on this query:

SELECT type_templates.name, type_templates.is_default, type_templates.created_at, type_templates.updated_at, type_templates.id FROM type_templates;

However the query is still being logged (the QEP in this case shows an index hit):

+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table          | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | type_templates | index | NULL          | idx_typetmpl_mp1 | 785     | NULL |   13 |   100.00 | Using index |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+----------+-------------+

Now, I understand 100% that the sequential scan is probably faster, but I think the problem I'm having is... why in the world are these being logged?

Answer 1

By default, MySQL logs ALL queries not using an index, but you can change that by setting a nonzero value for min_examined_row_limit - set it to 100 and these tables won't pollute your log anymore.

Answer 2

I see two problems

PROBLEM #1 : No WHERE clause

Any query without a where clause is bound to do full scans

PROBLEM #2 : There are way too few rows

Over the years, I have mentioned a special rule-of-thumb: If MySQL has to read more than 5% of the rows in a table when choosing an index during Query Optimization, it will go to another index. If no index fits this criteria, it does either a full table or full index scan.

Here are a few of my posts where I mention this

• Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?
• May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
• Mar 09, 2012 : index not being used
• Jan 18, 2012 : MySQL status variable Handler_read_rnd_next is growing a lot
• Jul 12, 2011 : MySQL very slow query when changing one WHERE field despite no index/key