MySQL Performance: MySQL => Another temorary table problem

Members

Help

Home

Home » Performance » MySQL » Another temorary table problem

Show: Today's Messages :: Show Polls :: Message Navigator

Another temorary table problem [message #2607]

Fri, 08 February 2008 07:09

dmiskiew
Messages: 1
Registered: February 2008

Junior Member

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                1
select_type       SIMPLE           SIMPLE	
table 	          cat              products
type 	          ref              ref
possible_keys 	  root_level1      category_id
key 		  root_level1      category_id
key_len 	  4                4
ref 		  const,const      supplo.cat.category_id
rows 	          20               4
Extra             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

[Updated on: Fri, 08 February 2008 14:04]

Report message to a moderator

Previous Topic:	Query slow first time, then fast
Next Topic:	Insert into a single table performance, any fundamental limitations?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 20:35:50 EDT 2009

Total time taken to generate the page: 0.01910 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.