GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Another temorary table problem

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Another temorary table problem

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