Joseph Piché, Web development professional with expertise in PHP, MySQL query optimization, Ajax, and XHTML.

On average, I guess 35% of my over time at work involves MySQL query building, optimizing and re-writing. I find it an enjoyable challenge, and recently I re-worked an existing framework for a new site which put my focus almost solely on SQL, queries, and the schema.

With a normalized database schema, SQL optimization tends to focus on aspects outside the query, like making sure indexes get put in the currect locations. But I happen to be stuck with large and slightly-non-normalized tables. So, in order to write queries against these tables for a site that averages 30k hits per month, I need to be able to see what MySQL is doing with each query. For this I would be lost without EXPLAIN and SQL_NO_CACHE.

I don’t like writing tutorials, and I don’t really have time. But I love linking to great documentation. Most of what I know about MySQL I learned from their documentation, and I encourage even experienced query writers to make sure they know the importance of testing query times with SQL_NO_CACHE or using a development environment with caching completely disabled. When it comes to web applications, even microseconds can hurt in the long run.

You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.