[Solved] MySQL inconsistent index usage for similar tables: \'Using index\' and \'Using where; Using index\'

EverSQL Database Performance Knowledge Base

MySQL inconsistent index usage for similar tables: \'Using index\' and \'Using where; Using index\'

Database type:

I'm having trouble optimizing a JOIN to use a composite index. My query is:

SELECT p1.id, p1.category_id, p1.tag_id, i.rating
    FROM products p1
      INNER JOIN (SELECT tag_id FROM tags WHERE category_id = 662) AS t
        ON t.tag_id = p1.tag_id
      LEFT JOIN (SELECT tax_id, rating FROM ratings WHERE category_id = 662) AS i
        ON i.tax_id = p1.category_id
    LIMIT 5

When using EXPLAIN, the INNER JOIN only uses the index, without touching the DB, but the LEFT JOIN returns 'Using where; Using index':

1   SIMPLE  tags    ref PRIMARY,category_id         PRIMARY        4    const                           87  100  Using index
1   SIMPLE  p1      ref category_id,category_id_2   topic_id       5    func                            40  100  Using where; Using index
1   SIMPLE  ratings ref category_id_2,category_id   category_id    8    const,data.p1.category_id        1  100  Using where; Using index

I have covering indexes on all three tables and both joins contain a WHERE clause.

Indexes:

tags(category_id, tag_id)
ratings(category_id, tax_id, rating)

Why does the first join use only the index and not the WHERE and how can I make the second join do the same? Tried all kinds of index combinations as well as forcing index usage, but nothing seems to work.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 7): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Prefer Direct Join Over Joined Subquery (query line: 10): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  4. Prefer Direct Join Over Joined Subquery (query line: 20): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  5. Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.
Optimal indexes for this query:
ALTER TABLE `ratings` ADD INDEX `ratings_idx_category_id_tax_id` (`category_id`,`tax_id`);
ALTER TABLE `tags` ADD INDEX `tags_idx_category_id` (`category_id`);
The optimized query:
SELECT
        p1_id,
        p1_category_id,
        p1_tag_id,
        i.rating 
    FROM
        (SELECT
            p1.id AS p1_id,
            p1.category_id AS p1_category_id,
            p1.tag_id AS p1_tag_id 
        FROM
            products p1 LIMIT 5) p1 
    INNER JOIN
        tags AS t 
            ON t.tag_id = p1.p1_tag_id 
    LEFT JOIN
        ratings AS i 
            ON i.tax_id = p1.p1_category_id 
            AND i.category_id = 662 
    WHERE
        (
            t.category_id = 662
        ) LIMIT 5

Related Articles



* original question posted on StackOverflow here.