Using mysql EXPLAIN to improve queries

Posted on 2007-08-05
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, FROM products WHERE products.dept="sport" GROUP BY ORDER BY,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.dept,, 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
    LVL 142

    Accepted Solution

    >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

    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


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now