# Join with order by optimization
**URL:** https://forums.percona.com/t/join-with-order-by-optimization/1105
**Category:** Other MySQL® Questions
**Created:** [March 9, 2009, 4:52pm UTC](https://forums.percona.com/t/join-with-order-by-optimization/1105 "2009-03-09T16:52:10Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@letssurf](https://forums.percona.com/u/letssurf)
#### Post date: [March 9, 2009, 4:52pm UTC](https://forums.percona.com/t/join-with-order-by-optimization/1105/1 "2009-03-09T16:52:10Z")
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.
---
### Author:  [@xaprb](https://forums.percona.com/u/xaprb)
#### Post date: [December 25, 2009, 7:18am UTC](https://forums.percona.com/t/join-with-order-by-optimization/1105/2 "2009-12-25T07:18:28Z")
If you are still having trouble with this, you should write back and include your EXPLAIN.
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [December 27, 2009, 7:21pm UTC](https://forums.percona.com/t/join-with-order-by-optimization/1105/3 "2009-12-27T19:21:41Z")
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).