Using mysql EXPLAIN to improve queries

Posted on 2007-08-05
Medium Priority
Last Modified: 2013-12-12
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?
Question by:dealclickcouk
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19636473
>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:
Key Buffer Size
Sort Buffer Size

LVL 29

Assisted Solution

fibo earned 1000 total points
ID: 19636929
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.
LVL 29

Expert Comment

ID: 22218155

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question