Why is my SQL query not using the table's composite index?

mysql not in ( select)
mysql not in (select subquery)
sql in and not in same query
mysql not exists
mysql not in table
mysql not equal
not in sql query
mysql not in list

I have a users table with the columns: id (primary key), type, external_id, external_type, created_at, updated_at

Indexes:
  • Primary (id)
  • Unique (external_id, external_type, type)
  • Non-unique (updated_at)

And a settings table with the columns: id, user_id, name, value, created_at, updated_at, type

Indexes:
  • Primary (id)
  • Unique (user_id, name)
  • Non-unique (user_id)
  • Non-unique (updated_at)
I execute the query:
SELECT users.id, users.type, users.external_id, users.created_at, users.updated_at,

  settings.id, settings.settings_id, settings.name, settings.value, 
  settings.created_at, settings.updated_at, settings.type

FROM users

  LEFT OUTER JOIN settings on settings.user_id = users.id

WHERE users.external_id=3 and users.external_type="Owner"
In the Explain report, I see that:
  • For the users table, the (external_id, external_type, type) index was identified as a possible key, but NOT used
  • The settings table uses the (user_id, name) index
Goal
  • I want to optimize this query
  • So I want to get the users table to use the (external_id, external_type, type) composite index
Things I’ve done to debug:
  • If I change the first line of the SELECT statement to remove users.created_at, users.updated_at, it uses the index
  • If I try adding a (external_id, external_type) non-unique index to the users table, it still doesn’t use it
  • If I change the query’s WHERE clause to add and users.type="Blah", it uses the index

What am I missing?

It is avoiding a double lookup

Your index is (external_id, external_type, type), but in order to get all the information necessary for the query it would have to use that index to find the rows, then use the id that is automatically included at the end of that index to look up the created_at and updated_at columns from the main table.

The optimizer makes the judgement that it would just be simpler to go straight to the main table to begin with, and so ignores the index.

You can see evidence of this fact with your statement:

If I change the first line of the SELECT statement to remove users.created_at, users.updated_at, it uses the index

Once you remove those columns, it no longer has to do a double lookup to complete the query. The single lookup from the index is what gets it to choose to use that index.

As for the following:

If I change the query’s WHERE clause to add and users.type="Blah", it uses the index

I would guess that the optimizer now thinks the double lookup is worth it, if it can reduce the rows enough with this more selective query. Understanding the reasoning of the optimizer is not always easy, but this seems like the most obvious reason.

Solution

To get it to use the index, you just need to make it so it doesn't need to perform a double lookup by making it a covering index.

(external_id,  external_type, type, created_at, updated_at)

This index will allow it to avoid the double lookup, as it can filter on the first columns, and then just use the remaining columns in the index to satisfy the SELECT for that table without having to jump back to the main table.

Why is my SQL query not using the table's composite index?, It is avoiding a double lookup. Your index is (external_id, external_type, type) , but in order to get all the information necessary for the query it  In your case, the query optimizer may think the table scan is the fastest if the statistics suggest most of the data in the table users with external_id = 3 and external_type = 'Owner' because no index on the table covers the columns being selected, and the query engine needs to do lookups for the data based on the index if index is used.

MySQL "NOT IN" query, To use IN, you must have a set, use this syntax instead: SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2). MYSQL Query for finding rows without certain associated rows in another table. Tag: mysql I am sure the answer is out there somewhere, but I am having a hard time articulating what I need, so I figured it best to give an example.

Not sure what version of mysql you are using. Before 8.0, mysql innodb does not persist the statistics, and the statistics in memory can hardly represent the data if your data is skewed. In your case, the query optimizer may think the table scan is the fastest if the statistics suggest most of the data in the table users with external_id = 3 and external_type = 'Owner' because no index on the table covers the columns being selected, and the query engine needs to do lookups for the data based on the index if index is used.

When you change to SELECT the only columns from the index, the index becomes the covering index and the query engine will not need to do the lookup.

MySQL Restrictions and Limitations :: 14 Known Issues in , With statement-based binary logging, the master writes the executed queries to the RENAME does not work with TEMPORARY tables or tables used in a  MySQL PHP API. Preface and Legal Notices. Introduction to the MySQL PHP API. Overview of the MySQL PHP drivers. MySQL Improved Extension. Overview. Quick start guide.

4. Query Performance Optimization, If your queries are bad, even the best-designed schema will not Find out whether the MySQL server is analyzing more rows than it They think MySQL will provide them with these 10 rows and stop executing the query, but what MySQL The access types range from a full table scan to index scans, range  A query need not be given all on a single line, so lengthy queries that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.

Optimising SELECTs and Other Queries, When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would To see what indexes a table has, use SHOW INDEX FROM tbl_name. MySQL will not continue searching for more rows for the current row  Answer: Oracle SQL not using an index is a common complaint, and it's often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to: · Bad/incomplete statistics - Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.

MySQL query optimization not using index, Or keep both of your tables, but in addition have a parent table with id , subtype and all columns these two tables have in common. The two other  For example, suppose you want to join two tables, Main and Sub, using the Root and ID fields, respectively. The Root field is a number type and the ID field is a string type. You can use the following custom SQL query to change the data type of Root from a number to a string so that you can join the Main and Sub tables using the Root and ID fields.

Comments
  • Your query and your table definitions are inconsistent. The query has the external fields coming from settings not users.
  • @GordonLinoff Sorry, I updated it!
  • What version of MySQL?
  • Please provide SHOW CREATE TABLE; there could be collation or other issues.
  • This is a great answer - thank you for explaining so thoroughly! :)
  • Sorry, I got the query wrong in the description trying to anonymize some company data -- updated it!!
  • And when you add users.type="Blah", the index becomes more selective and the optimizer decides to use this index. If your query can definitely benefit from the index, you add a query hint to force mysql to use the index