Archive for posts tagged ‘databases’
Book review: High Performance MySQL
Kevin Day, October 1st, 2007
High Performance MySQL is a great book for programmers new to database administration who are using MySQL. It has tips on writing faster queries, tuning database parameters, and indexing tables properly. While I had previously stumbled around with EXPLAIN statements and tried to guess what was going on with slow queries, this book demystified how MySQL really works.
This book is most useful for programmers looking for help beyond the standard introductory PHP/MySQL books and tutorials. It assumes that you know how to create a database-driven website but don’t know how to make it faster or deal with high traffic. It also covers the basics of databases such as transactions and index types.
I would have preferred a more detailed description of database tuning because there are a lot of settings to tweak, and it’s not easy to predict how they will work together. The book does provide a decent starting point though, and I now feel comfortable fiddling around with MySQL’s settings until I find the ones that work.
Overall, it’s a good book that will help any programmer using MySQL.
Reduced query time from 90 to 0.3 seconds
Kevin Day, June 20th, 2007Recently, I had a PHP script that used a MySQL query that joined three tables and then ordered the result by the index of the first table. It ran fine for a few weeks, but then one day the query started taking 90 seconds to run. Using MySQL’s EXPLAIN statement, I found it was reading from disk instead of memory, and that was probably causing the big slow-down.
Oddly, when I tried running the query in phpMyAdmin, the query ran in under a second, so it was hard to debug. But later when I ran the query from the MySQL console, it took 90 seconds just like the PHP script. I tried a few different versions of the query to make it run faster, and luckily all I had to do was drop the ORDER BY clause and the query time dropped to 0.3 seconds. The table was already sorted in the right order, so that clause wasn’t even necessary. I had just thrown it in there because I felt better explicitly specifying how it should be sorted. Guess I’ll know better next time.
I was surprised that the ORDER BY clause could make such a big difference. My guess is that the result (about 700 rows) got stored in a temporary table on the disk before it was sorted. However, I was even more surprised that phpMyAdmin gave a different query time than the console. Not sure why there would be a difference there.
