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

The more I use MySQL, the more I love it. I recently reviewed documentation for triggers when I ran into a pickle that needed to be solved in a short amount of time: ordering a query by TIMESTAMP.

I had a table with approx. 70k rows which had data continually inserted into it via cron job. I needed to query this table for the most recently inserted data given certain values. After re-organizing indexes and converting the table to InnoDB from MyISAM I was about to give up since (surprisingly) none of it gave any real improvement. The fundamental part of this query (the subquery in the SELECT expression which was ran over and over) was taking on average 0.022 seconds when ordering by a TIMESTAMP field which was automatically inserted by MySQL. When I used ORDER BY UNIX_TIMESTAMP(`field`) DESC LIMIT 1 instead, the query took 0.001 seconds on average. Since I didn’t wan’t MySQL converting it every time (really I don’t know what it does with times internally), I looked up some documentation and saw triggers. It saved my query and the table. I was able to make an additional integer field in the table and set a trigger on insert that did the conversion and stored the unix timestamp in the new field, then I could query the new unix timestamp field all I want and speed up this query even more.

It was an epiphany. Suddenly I saw SQL problems under a different light. I was even able to solve another complex problem by creating an event which would build rebuild a VIEW every hour. It was awesome until I discovered the production MySQL server was running 5.0 which doesn’t have events and I suspect handles VIEWs much more poorly than 5.1 which I was running locally. Of course I have no control over this server, and it handles uber-important data, so there is absolutely nothing I can do about it. So unfortunately I had to go back to the drawing board with that one.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.