Optimize mysql inner join query to use indexes

Question

I have a quite simple Mysql query that outputs products from category ordered by price descending.

SELECT
    p.id_product, p.price
FROM product p
INNER JOIN product_category pc
ON (pc.id_product = p.id_product AND pc.id_category=1234)
GROUP BY pc.id_product
ORDER BY p.price DESC

Since I have a lot of products in "product" table and even more product-category relations in "product_category" table this query lasts forever.

I have following indexes / primary keys defined:

table "product" - primary key (id_product)
table "product_category" - primary key (id_product, id_category), index(id_product), index(id_category)

But when I explain this query I get:

+----+-------------+-------+--------+--------------------+------------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys      | key        | key_len | ref                    | rows  | Extra                                        |
+----+-------------+-------+--------+--------------------+------------+---------+------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | pc    | index  | PRIMARY,id_product | id_product | 4       | NULL                   | 73779 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY            | PRIMARY    | 4       | mydb.pc.id_product     |     1 |                                              |
+----+-------------+-------+--------+--------------------+------------+---------+------------------------+-------+----------------------------------------------+

so... Using temporary; Using filesort - I think that this is the problem why everything is running so slow.

Since this query is executed a lot of times from a closed-source software I can't change it. But I want to optimize table / indexes so this query will run faster. Any help appreciated.

like I said - I can't change the query since it originates from closed-source software. So the only solution for me is to optimize the table(s) — j99
– j99, Commented Jan 12, 2015 at 14:01

Galz · Accepted Answer · 2015-01-12 14:02:08Z

1

You have a constant condition on id_category, and a JOIN condition in id_product.

I believe that if you create an index on (id_category, id_product), in this order, MySQL will be able to find relevant index entries for category 1234, and use them to find relevant product_ids to fetch from both tables.

Unfortunately I can't test this at the moment - I may try later. If you can give it a try you will find out very quickly...

answered Jan 12, 2015 at 14:02

Galz

6,8525 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

demogorgorn Over a year ago

Thanks, Galz!!! I changed indeces positions and my query from 600ms changed to 62 ms!!!!

Collectives™ on Stack Overflow

Optimize mysql inner join query to use indexes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related