Query Optimization

One of the more enjoyable parts of my job is finding and eliminating bottlenecks in our system. Yesterday I was faced with a problem of a moderate load average problem on our RSS servers. I could tell that our system has been growing and that there must be a query that just wasn't performing as optimally as when we first launched. I know the RSS code fairly well, so I dove in and starting looking for queries that could possibly be the culprit. It didn't take long to find a query that when executed on the live system took over 5 seconds to finish (table names and columns changed):

SELECT s.id, s.u, s.d
FROM s, i
LEFT JOIN p ON s.u = p.u
AND s.d = p.d
AND p.pr = 'RSS_E'
WHERE (p.val IS NULL OR p.val != '0')
AND s.fId = i.fId
AND s.lDItem < i.rTime LIMIT 1;

OK, so I looked at the columns of the tables and two columns (lDItem and rTime) were not indexed. So I added indexes to both columns and ran the query again. This time it actually took 10 seconds to execute! As my 4 year old daughter would say, "What in the hecka in the world?!?" I've never really seen MySQL slow down after adding indexes, but I know the query optimizer logic is very complex, so I decided to help it out a bit by giving it a hint as to which index to use:

SELECT s.id, s.u, s.d
FROM s, i use index (rTime)
LEFT JOIN p ON s.u = p.u
AND s.d = p.d
AND p.pr = 'RSS_E'
WHERE (p.val IS NULL OR p.val != '0')
AND s.fId = i.fId
AND s.lDItem < i.rTime LIMIT 1;

Now the query only takes .48 seconds to execute! Excellent. I also dropped the lDItem index and the query speed stayed the same, so I only really needed the one new index and a simple little addition to the query to achieve a 10x improvement in the query. This little fixed has eliminated all of the CPU bottlenecks on the RSS servers today. This stuff makes me so happy. :)

The point is that query optimization is not always a matter of just adding an index (many times it is, but not always). I had to experiment several times before I got the results that I expected.