Using MySQL EXPLAIN to optimize queries

MySQL is a powerful database and when used correctly you can easily get the most of it with little.

Using the query slow logs, query optimization, database optimization and some other techiniques is a great and reliable way to guarantee that your system can respond fast.

While developing, one great tool to use from MySQL is the EXPLAIN query. It returns, for a given query, the number of results, some other information, and the extra or missing indexes that you need.

Here is a brief demonstration of how the EXPLAIN query works:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

Result:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

In this case because ALL is the type for each table, MySQL will generate a combination of all rows to return in the query. In another words, this takes insane long time.

Anyway, for more optimization tips and the full manual of how

this works go to the MySQL Developer Articles.


					
Advertisements

About mcloide

Making things simpler, just check: http://www.mcloide.com View all posts by mcloide

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: