Another temorary table problem

  • Filter
  • Time
  • Show
Clear All
new posts

  • Another temorary table problem


    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?