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

Jul 22, 2009

As I will be working on kukku, my twitter app, i thought of applying for Twitter API whiltelisting. So i did it yesterday night, and today when i woke up found its approved. So cool. The mail which they sent is here.

They mention in the mail that it’ll take 48 hours to activate it but surprisingly when i checked my Tweetdeck, It was activated. 150 to 20,000 API requests per hour. Must utilize this…

As you can see in the heading of the blog, this is all about technical stuff. You can articles, source codes, samples etc on Linux, Windows, Apacahe, MySQL, PHP, WAMP Server, Code Igniter , Ajax, Javascript, Web 2.0, Web 3.0, CSS, HTML, Web development, SEO etc etc etc…