• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

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?
0
dealclickcouk
Asked:
dealclickcouk
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Also I notice a lot of queries that I looked at through EXPLAIN have "Using where; Using filesort"
filesort means that you either are missing indexed or cannot use indexes for that query.

apart from that, check especially the following 2 configuration options:
http://interworx.com/support/docs/iworx-cp/sysadmin/system-services/mysql/overview/howto-mysql-options
Key Buffer Size
Sort Buffer Size



0
 
Bernard S.CTOCommented:
Some comments on your question:
1 - The difference in running time between what is recorded in the log and what you see on your display might come from data transfer times over the network, eg if you have lots of text. There are few actions that might help you to lower this transfer time, unless you are not displaying all of the transferred text, a rather uncommon situation however.
2 - When running a SQL query, MySQL uses only ONE of the available indexes, and therefore optimizes on just one of the tables used. It tries to find the best one, but the other tables will be explored "sequentially" for each of the records selected from the optimized table/index: there will not be a general selection of all the relevant records, but rather for each of the 'optimized records' there will be 'individual access' to each of the records in the non-optimized [for this query] table. Note that it is important for these tables to be indexed, so that the individual access will be as fast as possible (if there is no index, than the whole table needs to be read!)
There are few options here to help you improve optimization, except maybe using a coumpound index (ie the combination of several fields) for your most selective table, and checking that 'individual accesses' will use an indexed access.
0
 
Bernard S.CTOCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now