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.subc at;
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?
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.subc
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.