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.