Using mysql EXPLAIN to improve queries
Posted on 2007-08-05
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:
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"
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?