Join with order by optimization

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

Bought the fantastic High Performance MySQL book.

Came across a problem today that I can’t really find a solution to. I’m not really at the point where I fully understand the indexes I’m trying to use.

I’ve read the blog post about Order by optimizations and the mysql manual regarding it.

I’m hoping for some more help of the forum, so here goes.

I have a table with possible millions of products which link to a table with possible hundreds of categories. I want to join a category to the products table ordered by the products name.

±---------------------------+| Tables_in_products_test |±---------------------------+| product | | category | | category_product | | … |Table product±---------------±--------------------±-----±----±------------------±---------------+| Field | Type | Null | Key | Default | Extra |±---------------±--------------------±-----±----±------------------±---------------+| product_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment || product_name | varchar(255) | NO | | NULL | || … |Table category±---------------±--------------------±-----±----±------------------±---------------+| Field | Type | Null | Key | Default | Extra |±---------------±--------------------±-----±----±------------------±---------------+| category_id | int(10) unsigned | NO | PRI | NULL | auto_increment || category_name | varchar(255) | NO | | NULL | || … |Table category_product±---------------±--------------------±-----±----±------------------±---------------+| Field | Type | Null | Key | Default | Extra |±---------------±--------------------±-----±----±------------------±---------------+| category_id | int(10) unsigned | NO | PRI | NULL | || product_id | bigint(20) | NO | PRI | NULL | || … |SELECT product.product_namer, category.category_nameFROM categoryINNER JOIN category_product USING(category_id)INNER JOIN product USING(product_id)WHERE category.category_id = 625ORDER BY product.product_nameLIMIT 25;

I’ve tried all sorts of different combinations of indexes to try and get the queries to run fast.

I managed to get the query to run fast if the category contained a large number of products. I guess just because it hit the limit 25 quicker.

2

If you are still having trouble with this, you should write back and include your EXPLAIN.

3

Try this:

SELECT
product.product_namer,
category.category_name

FROM category

INNER JOIN category_product USING(category_id)
INNER JOIN product USING(product_id)

WHERE
product.category_id = 625

ORDER BY
product.product_name

LIMIT 25;

and add an index on (category_id,product_name).