Correlated Subqueries are said to be “inefficient and likely to be slow,” but that doesn’t mean I’m not glad to have learned of them.
WordPress to_ping Query Optimization
The WordPress team has taken up the issue of performance optimization pretty seriously, and I look forward to the fruits of their efforts, but I’m also casting a critical eye on my own code. Thanks to caching and a hugely optimized query architecture, Scriblio is now performing better than ever, and I’m now looking at […] » about 400 words
Easy MySQL Performance Tips
Yes, I’m still trying to squeeze more performance out of MySQL. And since small changes to a query can make a big difference in performance…
Here are two really easy things to be aware of:
- Never do a
COUNT(*)(or anything*, says Zach). Instead, replace the*with the name of the column you’re searching against (and is hopefully indexed). That way some queries can execute entirely in the keycache (while * forces MySQL to read every matching row from the table). - When joining two large tables, but only searching against one, put the join statement at the end. Why join the two entire tables when you only have to join the matching rows?
I mention these because, well, I’ve known them forever, but upon seeing them again I realized I hadn’t really obeyed those simple rules in some of my queries.
Separately, there’s some pretty good info on what server variables affect what at mysqlperformanceblog too.
MySQL Error 28: Temp Tables And Running Out of Disk Space
Bam: MySQL error 28, and suddenly my queries came to a stop. Error 28 is about disk space, usually the disk space for temp tables. The first thing to do is figure out what filesystem(s) the tables are on. SHOW VARIABLES LIKE “%dir%” will return a number of results, but the ones that matter are […] » about 300 words
MySQL’s Slow Query Log
Zach suggested it last week, but it’s only now that I’ve gotten around to setting up MySQL’s slow query log.
It’s easy enough, you’ve just got to put a couple lines like this in your my.cnf (which is in /etc on my server):
log-slow-queries = /var/log/mysql/mysql-slow.log<br /> long_query_time = 10
This should get most people running, but this story in Database Journal offers a few more details. Potentially more useful is this guide to query and index optimization (though it’s probably a little out of date).