Results 1 to 2 of 2

Thread: Using MySQL's EXPLAIN command

  1. #1
    Member Newbie
    Join Date
    Oct 2006
    Posts
    90

    Default Using MySQL's EXPLAIN command

    While creating indexes in a database will go a long way to improving performance, you have to have an idea which columns in the tables will benefit from indexing. To do this you need to understand how mysql (or whatever database product you're using) handles queries. As most of our clients use mysql, I'll be talking about that speciffically here.

    Let's start with creating a very simple table to hold customer info, and add some records to play with:
    Code:
    CREATE TABLE `customers` (
      `cust_id` int(11) NOT NULL default '0',
      `first_name` varchar(30) default NULL,
      `last_name` varchar(30) default NULL,
      `st_address` varchar(40) default NULL,
      `city` varchar(40) default NULL,
      `country` varchar(40) default NULL,
      PRIMARY KEY  (`cust_id`),
      KEY `last_name` (`last_name`),
      KEY `country` (`country`),
      KEY `city` (`city`)
    );
    
    mysql> insert into customers (cust_id, first_name, last_name, st_address, city, country) values
        -> (1, 'Joe', 'Smith', '345 12th St.', 'Dallas', 'US'), 
        -> (2, 'Masao', 'Yasunori', '253 ichi-cho', 'Tokyo', "Japan"), 
        -> (3, 'Hans', 'Schwartz', '947 3 St.', 'Hannover', 'Germany'), 
        -> (4, 'Alex', 'Smith', '53 Main Rd.', 'London', 'England'),
        -> (5, 'Tom', 'Greenfield', '23 Wall St.', 'New York', 'US'),
        -> (6, 'Jane', 'Addington', '5 Cross Rd.', 'Essex', 'England')
        -> (7, 'Alex', 'Jones', '634 Granville St.', 'Vancouver', 'Canada');
    Now the question is what is mysql going to do to find a particular record or set of records. You can find out using the SQL EXPLAIN command. Compare the following two queries, one to find every record where the last name is Smith, and one to find every record where the first name is Alex:

    Code:
    mysql> select * from customers where last_name = "Smith";
    +---------+------------+-----------+--------------+--------+---------+
    | cust_id | first_name | last_name | st_address   | city   | country |
    +---------+------------+-----------+--------------+--------+---------+
    |       1 | Joe        | Smith     | 345 12th St. | Dallas | US      |
    |       4 | Alex       | Smith     | 53 Main Rd.  | London | England |
    +---------+------------+-----------+--------------+--------+---------+
    2 rows in set (0.00 sec)
    
    mysql> select * from customers where first_name = 'Alex';
    +---------+------------+-----------+-------------------+-----------+---------+
    | cust_id | first_name | last_name | st_address        | city      | country |
    +---------+------------+-----------+-------------------+-----------+---------+
    |       7 | Alex       | Jones     | 634 Granville St. | Vancouver | Canada  |
    |       4 | Alex       | Smith     | 53 Main Rd.       | London    | England |
    +---------+------------+-----------+-------------------+-----------+---------+
    2 rows in set (0.00 sec)
    To use the EXPLAIN command to see how mysql handles these requests you just add 'EXPLAIN' to the begining of the command. This will give you an analysis on what mysql is doing to handle your request.

    Code:
    mysql> explain select * from customers where last_name = "Smith";
    +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
    | id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra       |
    +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
    |  1 | SIMPLE      | customers | ref  | last_name     | last_name |      31 | const |    2 | Using where |
    +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
    1 row in set (0.01 sec)
    
    mysql> explain select * from customers where first_name = 'Alex';
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | customers | ALL  | NULL          | NULL |    NULL | NULL |    7 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    Now, in terms of optimizing the query, the two columns that you want to look at first are the 'type' and 'rows' column. The type column tells you what kind of join mysql has had to do, and the rows column tells you how many records mysql retrived from that table. There is a whole heirarchy of join types that mysql can use, and the higher in that heirarchy your query is the better. While you can't always avoid it, you want to avoid the 'ALL' type whenever possible. It won't matter for a very small table like this, but if mysql has to go through every single record in a table with 1,000,000 records, that's going to take a while, and your performance is going to really suffer.

    The way to solve this is to see if adding an index will allow you to use a better type of query. Comparing the two queries above you can see that the query searching through the last names only had to access two rows, while the search for first names required that every row in the table. This is because the last_name column has a key/index associated with it, which mysql can use to find the records more efficently. Adding an index on the first name column will make the second query as efficent as the first.

    Code:
    mysql> create index cust_first_name on customers (first_name);
    Query OK, 7 rows affected (0.07 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    
    mysql> explain select * from customers where first_name = 'Alex';     
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-------------+
    | id | select_type | table     | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-------------+
    |  1 | SIMPLE      | customers | ref  | cust_first_name | cust_first_name |      31 | const |    2 | Using where |
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-------------+
    1 row in set (0.00 sec
    Note that there is more to all of this, and that indexing can make an even bigger difference if you are running queries that join multiple tables. Mysql has devoted an entire page in its documentation to using the EXPLAIN command. If you're going to be using mysql for your site I would suggest you take a look.

  2. #2
    Junior Member Newbie
    Join Date
    Mar 2009
    Posts
    1

    Default

    Hello,

    Just wanted to point out that the code sample...
    insert into customers (cust_id, first_name, last_name, st_address, city, country) values
    -> (1, 'Joe', 'Smith', '345 12th St.', 'Dallas', 'US'),
    -> (2, 'Masao', 'Yasunori', '253 ichi-cho', 'Tokyo', "Japan"),
    -> (3, 'Hans', 'Schwartz', '947 3 St.', 'Hannover', 'Germany'),
    -> (4, 'Alex', 'Smith', '53 Main Rd.', 'London', 'England'),
    -> (5, 'Tom', 'Greenfield', '23 Wall St.', 'New York', 'US'),
    -> (6, 'Jane', 'Addington', '5 Cross Rd.', 'Essex', 'England')
    -> (7, 'Alex', 'Jones', '634 Granville St.', 'Vancouver', 'Canada');
    ... should read ...
    insert into customers (cust_id, first_name, last_name, st_address, city, country) values
    -> (1, 'Joe', 'Smith', '345 12th St.', 'Dallas', 'US'),
    -> (2, 'Masao', 'Yasunori', '253 ichi-cho', 'Tokyo', 'Japan'),
    -> (3, 'Hans', 'Schwartz', '947 3 St.', 'Hannover', 'Germany'),
    -> (4, 'Alex', 'Smith', '53 Main Rd.', 'London', 'England'),
    -> (5, 'Tom', 'Greenfield', '23 Wall St.', 'New York', 'US'),
    -> (6, 'Jane', 'Addington', '5 Cross Rd.', 'Essex', 'England'),
    -> (7, 'Alex', 'Jones', '634 Granville St.', 'Vancouver', 'Canada');
    Thanks,
    Bud

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •