MySQL Forums
Forum List  »  Optimizer & Parser

Re: table/query help
Posted by: Rick James
Date: March 04, 2010 10:22PM


A key-value schema like yours is very flexible, but extremely poor when it comes to perfromance. You have an N-way JOIN where the points of the "star" control which rows you want.

You should really move some likely fields to the main table and add indexes on them. This will give you a fighting chance.

OK, maybe I am too harsh. This will let your query run 10x faster, but won't fix the other 100x that will kill you later.
INDEX(cat, iid)

Another (smaller) optimization... A subquery could have avoided most of the cost of "Using temporary; Using filesort". The big work is finding a list of iids (inner query). The temporary/filesort is costly because of hauling around lots of 'description', etc, only to throw most of them away. The outer query would be a JOIN back to ds_item to get them.

Options: ReplyQuote

Written By
a g
March 03, 2010 12:36PM
Re: table/query help
March 04, 2010 10:22PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.