Faster & Stronger MySQL
Oct 08, 2009, 19:04 (1 Talkback[s])
(Other stories by Sean Hull)
I've been independent consulting for over twelve years now. Over
that time, the most common trouble spot has been errant SQL. SQL is
the language you use to communicate with the database, but it is
also the frame with which you lay down your problem, and feed it to
a computer for solving.
"Simplify the SQL
Sometimes, in the rush to get something out the door, we write SQL,
which is asking for more than it needs. Perhaps we need two rows,
but we ask for the whole table. During development, the table had
only ten rows, so the code was very fast. As it's rolled out in
production, we find it getting slower and slower. Turns out this
little bit of code is on the main page of the website, and new
users to the site all hit it. Now the table has ten million rows,
and it is indeed a showstopper. We fire up our monitoring tools,
identify this query as a very slow query, a repeat offender.
Adjusting the SQL to read just the two rows our application wants,
it still delivers the application what it needs, but now taxes the
database 1/5millionth as much. Now that's a big savings, and now
the database has time to do all the other things its users are
asking of it, and still has time to rest besides!"