2

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?

  • 1
    In my opinion logging tables not using indexes is too noisy to turn on. – Rick James Sep 3 '15 at 22:39
3

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.

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

  • No where clause is used because all rows are pulled every time. I'm trying to get one of the developers to cache this table in Redis, however at this moment it is deemed acceptable to continue doing this (and in all reality it's probably fine, I was just wanting to understand why that query is being logged). – chucky_z Sep 22 '15 at 18:59

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.