Nov 2 2011

I’ve been going through the MySQL slow query log at my new job to optimize our applications performance. It’s a bit more scary making changes when you’re the “new guy” because there are a lot of “gotchas” you haven’t quite figured out in the new platform. Well one of the queries that I wanted to optimize lead me to get rid of some needless LEFT JOINS and trim down the amount of fields returned in the result set. Aside from testing the application as a whole after making my modification I needed a fast way to verify the exact same data was being returned for all 50,000 rows. One option was to append each field to a string and then convert that into an MD5 hash for each variation of the query. That seemed half assed too me so I decided to export the results to a CSV file then run a diff against the two from the linux shell.

STEP 1 This is really easy to do. The diff command will tell you exactly where the differences are in the files giving you somewhere to start debugging. In PHPMyAdmin take your current production stable query, run it, click the export tab, select CSV, and save it to a file. Do the same for your shiny new optimized query.

STEP 2 Now that you have both files saved to your file system. Run the DIFF command:

If there are NO differences the command will return NO output. If there are any differences you would get some output like this:

I hope this helps someone out and if there is a better way to do this please leave a comment.

diff, mysql, query optimization

Inheritance in CodeIgniter: Adding More Functionality To Your Models Working Around CodeIgniters Default Session Library