Link to home
Start Free TrialLog in
Avatar of dealclickcouk
dealclickcouk

asked on

Using mysql EXPLAIN to improve queries

I've been experiancing mysql problem's, ie queries taking far too long to complete when the webserver gets busy (busy=10-20 concurrent users), so I enabled slow queries log to see which stmts aren't performing well.

I've then been using EXPLAIN to improve indexing on queries.  What I'm not sure about is the "Extra" information that EXPLAIN provides.

ie on this stmt:

SELECT products.dept, products.cat FROM products WHERE products.dept="sport" GROUP BY products.cat ORDER BY products.cat,products.subcat;

EXPLAIN says its using ket details which is fields:

dept,cat,subcat,make

But states:  Using where; Using temporary

Why is it using a temp table?

Running this query now takes 500ms, but in the log it has a query time of 70 secs!

Also I notice a lot of queries that I looked at through EXPLAIN have "Using where; Using filesort"

ie
SELECT products.id, products.dept, products.cat, products.subcat, products.Make, products.Product, products.Description, products.Price, products.thumbname, products.imageid, products.img_width , products.img_height, products.Sellers, products.Rating, products.Review FROM products WHERE (products.subcat='ladies fashion tops'  )   ORDER BY status ASC, clicks DESC  LIMIT 0 ,20;

What is the best method for making an index ie which fields would ideally be included for the above 2 stmts?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
dealclickcouk,

Sure you did not get lots of answers.
Any problem with mine? Are you now OK with EXPLAIN?
Please consider closing the question as you will find appropriate.
Thx.
B.