hobodave.com

How to ask for help with MySQL performance

30 Jul 2009

Since discovering StackOverflow I’ve become hopelessly addicted (see the badge to the right). I answer the mysql, php, zend-framework, and javascript categories mostly.

My favorite category is mysql. These can be some of the most challenging questions to answer, particularly the query optimization ones. I often answer, having learned something myself in the process.

Unfortunately, given the vast number of users, there are a lot of communication inconsistencies. It drives me batshit-crazy to read a textual description of a table. So I created a simple guide to getting people to help you with MySQL queries.

• SHOW CREATE TABLE - this is the best way to show someone your table schema. Don’t try to describe it using plain-English, it just isn’t good enough. This exposes your columns, field types (you said TIMESTAMP but you really have a DATETIME), and your indices all in one query.
• USE EXPLAIN - EXPLAINing your queries shows others where to start. It’s ok if you don’t understand it completely, but read the documentation for the basics.
• USE \G - You have two ways to delimit your MySQL queries when using the mysql client. Using the DELIMITER, ; by default - or using \G. This turns your columnar data into row data and limits the width to 80 chars. This is ideal for displaying on the web.

Read the full article on StackOverflow.