1

There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too.

The Issue

We have a smallish table 'products_keys' (about 10,000 rows) that contains records of products. We have a second table 'products_ordering' that contains information on how to order these records when displayed; essentially, a method to override standard alphanumeric ordering. If table 'products_ordering' doesn't contain additional ordering information, then it fallsback to standard ordering. In either case, the records from A must be returned, so a left join is used.

On trying to order by the values held in this left joined table, the execution time increases by 2-3 orders of magnitude. While the performance hit is JUST about acceptable at the moment, this will rapidly degrade as the DB grows and is not scalable.

The query

SELECT pk.p_key_id
FROM products_keys pk
LEFT JOIN products_ordering AS po ON pk.p_key_id = po.p_key_id
ORDER BY po.order_priority ASC

Explain

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  pk  index   NULL    activated   1   NULL    12342   Using index; Using temporary; Using filesort
1   SIMPLE  po  ref     p_id,p_key_id,order_priority    p_id    4   plv4_dev.pk.p_key_id    1   

I presume that this is caused by the fact MySQL can't find an appropriate index to perform the sort. I tried adding an index on product_ordering (p_key_id, order_priority) but this didn't do much.

Are there methods to allow this type of query to use an index when sorting? Alternatively, is there a better query form I should be using?

Thank you very much in advance

  • 1
    Try adding an index on order_priority alone. Do you use limit in the real world query? If not, then the selected plan is perhaps the best - as long as the server needs to read and join all the rows, sorting is not necessarily the largest cost. – Kostja Apr 13 '15 at 20:50
  • @Kostja - thanks for this. This didn't seem to have any effect. I think we will end up redesigning some of our application features to avoid this bottleneck, but I'm still intrigued if there is a better technique out there. Thank though! -bf – flukeflume Apr 14 '15 at 9:27
  • For the record - As we will only be returning a small set of results, we've decided to rewrite our app logic and perform 2 separate queries. These separate queries are then processed and combined in PHP. The resulting execution time didn't quite improve by 2 full orders of magnitude, but it was close! – flukeflume Apr 14 '15 at 11:10
  • ORDER BY po.order_priority ASC effectively gives on predictable ordering to the items missing from po. Perhaps you want ORDER BY pk.something, po.order_priority ASC? – Rick James Apr 17 '15 at 3:14
  • What percent of rows are missing from po? – Rick James Apr 17 '15 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.