Do very small MySQL tables ignore indexes?

50
February 16, 2019, at 11:10 PM

After turning on log_queries_not_using_indexes, I noticed that one query was rapidly filling up the slow query log:

SELECT abc.* FROM abc WHERE abc.id NOT IN ( SELECT DISTINCT abc_id FROM zyx WHERE id = 12345 );

abc is very small, just 3 rows of data. zyx is relatively large with over 100,000 rows of data.

abc.id has an index, but when I EXPLAIN the query, the index isn't listed under either key nor possible_keys. This explains why the query is showing in the slow log, but my question is, why isn't it using the index?

In short, I have two questions:

  1. Do very small tables ignore indexes? I could see why, it doesn't save much time to use an index on 3 rows of data.
  2. If so, how can I prevent this query from flooding my slow query log?

Thank you for your time! :)

Additional information, if needed:

I have run ANALYZE TABLE abc as I've read sometimes fixes the issue. I have also restarted MariaDB since adding the index.

More of the EXPLAIN: select_type=PRIMARY, table=abc, type=ALL, possible_keys=NULL, key=NULL, key_len=NULL, ref=NULL, rows=3, Extra=Using where

Answer 1

Do very small tables ignore indexes?

Yes. When the entire table can be read in a single disk access, there's no point in performing a separate disk access to read the index.

If so, how can I prevent this query from flooding my slow query log?

Turn off log_queries_not_using_indexes. This is one of the reasons why it isn't on by default.

Answer 2

NOT IN ( SELECT ... ) is very poorly optimized, especially in older versions.

Change to this:

SELECT  abc.*
    FROM  abc
    LEFT JOIN  zyx  ON zyx.abc_id = abc.id
    WHERE  zyx.abc_id IS NULL;
AND  zyx.id = 12345 ;

For zyx, have either INDEX(id, abc_id) or INDEX(abc_id, id)

If zyx.id is the PRIMARY KEY, your query does not make much sense -- why test for a single row (12345)?

READ ALSO
How can I insert an array to database table with new rows

How can I insert an array to database table with new rows

I'm trying to insert an array size 2000 into a database table that contains 13 columns, from array[0] to array[12] in the first row then from array[13] to array[25] in the second row

63
Append a new field to an existing array

Append a new field to an existing array

I'm trying to add the same team name to each entry in the arrayA user can currently add multiple names in a single form submission

42
Write a SQL query to list the recording id and recording date of all recordings that appear on all of the cds in the cd table

Write a SQL query to list the recording id and recording date of all recordings that appear on all of the cds in the cd table

I've probably tried writing this query 30 different ways, I and just can't seem to figure it outHere is the problem:

53
PHP Design practise. What's the best place put MYSQL queries

PHP Design practise. What's the best place put MYSQL queries

Fellow programmersI'm inexperienced and I'm seeking knowledge

54