web analytics

MySQL query execution order

I was working over a couple of projects in which I was doing MySQL optimisation. I was analysing mysql server using Jet Profiler for MySQL and I was wondering to see a couple of very slow queries in the pages. I was trying to figure out methods to sort this out.  A typical MySQL query will be like this.

                SELECT
                    customer_name,
                    customer_basename,
                    customer_basenamecount,
                    count(trackinvoice.id) as invoicecount
                FROM
                    trackinvoice,
                    customer
                WHERE
                    trackinvoice_invoicecustomerid = customer.id
                GROUP BY
                    trackinvoice_invoicecustomerid
                ORDER BY invoicecount DESC
                LIMIT 15

 

When I looked for MySQL query execution order, which is as follows

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

Which means the query is not executed the way we write them.

To optimise the performance, we have to make sure for every query the server overhead is kept to the minimum as possible. So when you are writing MySQL queries, keep in mind this order and write them instead of executing and checking..

More to come, stay tuned…

Resources from: Ben Nadal and NT Srikanth’s blogs

2 Comments

  1. azmina baboo

    r u talkin abt smthng advanced? bcoz this is confusing me..if u r talkin abt the order in which we write a query, then select clause should come first no? sry, if u meant smthng else other than the order of writin a query..me just got confused..

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>