Niju Mohan

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