Hello,
I have a table ‘categories’ which contains categories and a table ‘categories_tree’, which organizes my categories into a tree. The user can select a brach of the tree. Then I need to show him the list of items, that belongs to this selected branch. Products can belong only to categories, that do not have any subcategories. Here how I’m trying to do it:
Here is sql:
SELECT products.id
FROM products
JOIN categories_trees AS cat
ON products.category_id = cat.category_id
WHERE cat.root_level1 = 2 # id of categories tree branch
AND cat.has_child = 0
ORDER BY date DESC
LIMIT 0 , 10
and here is explain:
id 1 1select_type SIMPLE SIMPLE table cat productstype ref refpossible_keys root_level1 category_idkey root_level1 category_idkey_len 4 4ref const,const supplo.cat.category_idrows 20 4Extra Using temporary; Using where Using filesort
Index on categories_trees table: root_level1 (root_level1, has_child)
Index on products table: category_id (category_id, date)
Is it possible, to eliminate Using temporary?
Thanks
Darek