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.
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.
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
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