Jam's Box

Leaning Note

0%

Posted on Disqus:

What is Explain?

MySQL explain syntax: Explain {SQL}

Explain can provide a lot of information about how mysql executes SQL.

The information include:

  • Order of reading table

  • Use index or not

  • Possible index can be used

  • Which index it use

  • How to access table data (type)

  • How many rows were scanned

When use it?

  • Fixed the slow query

  • Validate whether MySQL execution plan is same as you thinking

  • Check whether index was used or using correct index.

What information it provide?

Here is a simple explain SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> Explain SELECT a.question_id, count(distinct a.question_id) count  FROM survey_log a GROUP BY a.question_id ;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using filesort |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

1  row  in  set, 1 warning (0.00 sec)

You can see many column show, and you can check the detail and meaning on MySQL Documentation.

Table - EXPLAIN Output Columns

Column JSON Name Meaning
id select_id The SELECT identifier
select_type None The SELECT type
table table_name The table for the output row
partitions partitions The matching partitions
type access_type The join type
possible_keys possible_keys The possible indexes to choose
key key The index actually chosen
key_len key_length The length of the chosen key
ref ref The columns compared to the index
rows rows Estimate of rows to be examined
filtered filtered Percentage of rows filtered by table condition
Extra None Additional information

We will focus on type, possible_keys, key, and rows these three type.

  • key - key show that which index used on search.

  • possible_keys - show all possbile index can be used

    Example:

    • This one is no index for question_id
      1
      2
      3
      4
      5
      6
      mysql> Explain SELECT a.question_id, count(distinct a.question_id) count FROM survey_log a GROUP BY a.question_id ;
      +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
      | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
      +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
      |  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
      +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    • This one have one index for question_id and one index for question_id and answer_id.
      You can see the column possible_keys show two possible index and column key display index used is idx_survey_log_question_id.
      1
      2
      3
      4
      5
      6
      7
      mysql> Explain SELECT a.question_id, count(distinct a.question_id) count FROM survey_log a GROUP BY a.question_id ;
      +----+-------------+-------+------------+-------+--------------------------------------------------+----------------------------+---------+------+------+----------+-------------------------------------+
      | id | select_type | table | partitions | type  | possible_keys                                    | key                        | key_len | ref  | rows | filtered | Extra                               |
      +----+-------------+-------+------------+-------+--------------------------------------------------+----------------------------+---------+------+------+----------+-------------------------------------+
      |  1 | SIMPLE      | a     | NULL       | range | idx_survey_log_question_id,question_id_answer_id | idx_survey_log_question_id | 5       | NULL |    3 |   100.00 | Using index for group-by (scanning) |
      +----+-------------+-------+------------+-------+--------------------------------------------------+----------------------------+---------+------+------+----------+-------------------------------------+
      1 row in set, 1 warning (0.01 sec)

  • type - describe how table data are accessed.
    There are many type: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL

    • system - use this When table has only one row.

      • Example 1 - system table
        In this case, the sys.version table has only one row.

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        mysql> use sys;
        Database changed

        mysql> SELECT * FROM version;
        +-------------+---------------+
        | sys_version | mysql_version |
        +-------------+---------------+
        | 2.1.1       | 8.0.28        |
        +-------------+---------------+
        1 row in set (0.00 sec)

        mysql> EXPLAIN SELECT * FROM version;
        +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
        | id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
        +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
        |  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
        |  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
        +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
        2 rows in set, 1 warning (0.00 sec)

      • Exampe 2 - Not system table but has only one row

        • CREATE and INSERT SQL

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          12
          13
          14
          15
          16
          17
          18
          19
          20
          21
          22
          --- user ---
          DROP TABLE IF EXISTS `user`;
          CREATE TABLE `user` (
              `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
              `username` VARCHAR(16) NOT NULL,
              `email` VARCHAR(255) NULL,
              `password` VARCHAR(32) NOT NULL,
              `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
          INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');

          --- user2 ---
          DROP TABLE IF EXISTS `user2`;
          CREATE TABLE `user2` (
              `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
              `username` VARCHAR(16) NOT NULL,
              `email` VARCHAR(255) NULL,
              `password` VARCHAR(32) NOT NULL,
              `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          INSERT INTO `user2` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');

        • SELECT
          In this case, you will find that only SQL for table user use type system but not for table user2.
          It’s because user use MyISAM engine and user2 use InnoDB engine. InnoDB doesn’t maintain table sizes reliably, so the query optimizer can’t be sure that the table has exactly 1 row. This is mention 

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          12
          13
          14
          15
          16
          17
          18
          19
          20
          21
          22
          23
          24
          25
          26
          27
          28
          29
          30
          31
          32
          mysql> SELECT * FROM user;
          +----+----------+---------------+----------+---------------------+
          | id | username | email         | password | create_time         |
          +----+----------+---------------+----------+---------------------+
          |  1 | jam      | xxx@gmail.com | 1234     | 2022-06-18 17:32:01 |
          +----+----------+---------------+----------+---------------------+
          1 row in set (0.00 sec)

          mysql> SELECT * FROM user2;
          +----+----------+---------------+----------+---------------------+
          | id | username | email         | password | create_time         |
          +----+----------+---------------+----------+---------------------+
          |  1 | jam      | xxx@gmail.com | 1234     | 2022-06-18 17:32:02 |
          +----+----------+---------------+----------+---------------------+
          1 row in set (0.00 sec)

          mysql> EXPLAIN SELECT * FROM user WHERE id=1;
          +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
          | id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
          +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
          |  1 | SIMPLE      | user  | NULL       | system | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
          +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
          1 row in set, 1 warning (0.00 sec)

          mysql> EXPLAIN SELECT * FROM user2 WHERE id=1;
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          |  1 | SIMPLE      | user2 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          1 row in set, 1 warning (0.00 sec)

    • const - when you compare all parts of PRIMARY KEY or UINIQUE index to consta values

      • Example
        • CREATE AND INSERT
          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          DROP TABLE IF EXISTS `user`;
          CREATE TABLE `user` (
            `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
            `username` VARCHAR(16) NOT NULL,
            `email` VARCHAR(255) NULL,
            `password` VARCHAR(32) NOT NULL,
            `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');

        • SELECT 
          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          12
          13
          14
          15
          mysql> SELECT * FROM user;
          +----+----------+---------------+----------+---------------------+
          | id | username | email         | password | create_time         |
          +----+----------+---------------+----------+---------------------+
          |  1 | jam      | xxx@gmail.com | 1234     | 2022-06-19 06:13:27 |
          +----+----------+---------------+----------+---------------------+
          1 row in set (0.00 sec)

          mysql> EXPLAIN SELECT * FROM user WHERE id = 1;
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          |  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
          +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
          1 row in set, 1 warning (0.00 sec)

    • eq_ref - One row is read from this table for each combination of rows from the previous tables and It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

      And this mean:

      • table1 join table2
      • all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

      Example - user and user_information

      • CREATE AND INSERT
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        -- user --
        DROP TABLE IF EXISTS `user`;
        CREATE TABLE `user` (
          `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
          `username` VARCHAR(16) NOT NULL,
          `email` VARCHAR(255) NULL,
          `password` VARCHAR(32) NOT NULL,
          `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam2', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam3', 'xxx@gmail.com', '1234');
        -- user_information --
        DROP TABLE IF EXISTS `user_information`;
        CREATE TABLE `user_information` (
          `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
            `user_id` INT NOT NULL UNIQUE,
          `address` VARCHAR(255) NOT NULL,
          `phone` int(15) NOT NULL,
          `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        INSERT INTO `user_information` (`user_id`, `address`, `phone`)  VALUES(1, 'jam-address', '12334567');
        INSERT INTO `user_information` (`user_id`, `address`, `phone`)  VALUES(2, 'jam2-address', '56677888');
        INSERT INTO `user_information` (`user_id`, `address`, `phone`)  VALUES(3, 'jam3-address', '56677888');
      • SELECT
        Here is a point that If table user_information has fewer rows, the type might be ALL because execution plan thought scan all type is faster than use index.
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        mysql> EXPLAIN SELECT * FROM user ,user_information WHERE user.id = user_information.user_id;
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        | id | select_type | table            | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        |  1 | SIMPLE      | user             | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |   100.00 | NULL  |
        |  1 | SIMPLE      | user_information | NULL       | eq_ref | user_id       | user_id | 4       | test.user.id |    1 |   100.00 | NULL  |
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        2 rows in set, 1 warning (0.00 sec)

        mysql> EXPLAIN SELECT * FROM user INNER JOIN user_information ON user.id = user_information.user_id;
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        | id | select_type | table            | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        |  1 | SIMPLE      | user             | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |   100.00 | NULL  |
        |  1 | SIMPLE      | user_information | NULL       | eq_ref | user_id       | user_id | 4       | test.user.id |    1 |   100.00 | NULL  |
        +----+-------------+------------------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
        2 rows in set, 1 warning (0.00 sec)

    • ref - All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index.

    And this mean:

    1. select table where the column which has not PRIMARY KEY AND UNIQUE index.
    2. table1 join table2 and all parts of an index are used by the join and the index is not a PRIMARY KEY or UNIQUE NOT NULL index.

    Example:

    • CREATE AND INSERT

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
        -- user --
        DROP TABLE IF EXISTS `user`;
        CREATE TABLE `user` (
        `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(16) NOT NULL,
        `email` VARCHAR(255) NULL,
        `password` VARCHAR(32) NOT NULL,
        `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        CREATE INDEX idx_user_email ON `user` (`email`);
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam2', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam3', 'xxx@gmail.com', '1234');
        DROP TABLE IF EXISTS `post`;
        CREATE TABLE `post` (
          `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
            `user_id` INT NOT NULL,
          `title` VARCHAR(16) NOT NULL,
          `content` TEXT NULL,
          `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        CREATE INDEX idx_post_user_id ON `post` (`user_id`);

        DROP PROCEDURE IF EXISTS loop_insert_post;
        DELIMITER //  
        CREATE PROCEDURE loop_insert_post()
        BEGIN
        DECLARE i INT DEFAULT 1; 
        WHILE (i <= 50) DO
            INSERT INTO `post` (`user_id`, `title`, `content`) VALUES (1, CONCAT('jam-post', i), 'content');
            INSERT INTO `post` (`user_id`, `title`, `content`) VALUES (2, CONCAT('jam-post', i), 'content');
            SET i = i+1;
        END WHILE;
        END;
        //
        DELIMITER ;
        CALL loop_insert_post();
        DROP PROCEDURE IF EXISTS loop_insert_post;
        ```		
      * SELECT
        Here is a point that If table post has fewer rows, the type might be ALL because execution plan thought scan all type is faster than use index.
        ```sql
        mysql> EXPLAIN SELECT * FROM user WHERE email = 'xxx@gmail.com';
        +----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
        | id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
        +----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
        |  1 | SIMPLE      | user  | NULL       | ref  | idx_user_email | idx_user_email | 768     | const |    3 |   100.00 | NULL  |
        +----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
        1 row in set, 1 warning (0.00 sec)

        mysql> EXPLAIN SELECT * FROM user u JOIN post p ON u.id = p.user_id;
        +----+-------------+-------+------------+------+------------------+------------------+---------+-----------+------+----------+-------+
        | id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref       | rows | filtered | Extra |
        +----+-------------+-------+------------+------+------------------+------------------+---------+-----------+------+----------+-------+
        |  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY          | NULL             | NULL    | NULL      |    3 |   100.00 | NULL  |
        |  1 | SIMPLE      | p     | NULL       | ref  | idx_post_user_id | idx_post_user_id | 4       | test.u.id |   50 |   100.00 | NULL  |
        +----+-------------+-------+------------+------+------------------+------------------+---------+-----------+------+----------+-------+
        2 rows in set, 1 warning (0.00 sec)

        mysql> EXPLAIN SELECT * FROM user, post WHERE user.id = post.user_id;
        +----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+
        | id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref          | rows | filtered | Extra |
        +----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+
        |  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY          | NULL             | NULL    | NULL         |    3 |   100.00 | NULL  |
        |  1 | SIMPLE      | post  | NULL       | ref  | idx_post_user_id | idx_post_user_id | 4       | test.user.id |   50 |   100.00 | NULL  |
        +----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+
        2 rows in set, 1 warning (0.00 sec)

    • fulltext - The join is performed using a FULLTEXT index.

    • ref_or_null - This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values.

    • index_merge - this join type indicates that the Index Merge optimization is used.

    • unique_subquery - is just an index lookup function that replaces the subquery completely for better efficiency.

    • index_subquery - This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries

    • range - Only rows that are in a given range are retrieved, using an index to select the rows.

      • CREATE AND INSERT
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        DROP TABLE IF EXISTS `event_log`;
        CREATE TABLE `event_log` (
        `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
        `title` VARCHAR(50) NOT NULL,
        `message` TEXT NULL,
        `status_code` INT NOT NULL,
        `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        CREATE INDEX idx_event_log_status_code ON `event_log` (`status_code`);
        DROP PROCEDURE IF EXISTS loop_insert_event_log;
        DELIMITER //  
        CREATE PROCEDURE loop_insert_event_log()
        BEGIN
        DECLARE i INT DEFAULT 1; 
        WHILE (i <= 1000) DO
            INSERT INTO `event_log` (`title`, `message`, `status_code`) VALUES ( CONCAT('message', i), 'content', FLOOR(RAND()*5));
            SET i = i+1;
        END WHILE;
        END;
        //
        DELIMITER ;
        CALL loop_insert_event_log();
        DROP PROCEDURE IF EXISTS loop_insert_event_log;
      • SELECT
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        mysql> EXPLAIN SELECT * FROM event_log WHERE status_code > 3;
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        | id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        |  1 | SIMPLE      | event_log | NULL       | range | idx_event_log_status_code | idx_event_log_status_code | 4       | NULL |   11 |   100.00 | Using index condition |
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        1 row in set, 1 warning (0.00 sec)

        mysql> EXPLAIN SELECT * FROM event_log WHERE status_code IN (2,5);
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        | id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        |  1 | SIMPLE      | event_log | NULL       | range | idx_event_log_status_code | idx_event_log_status_code | 4       | NULL |  196 |   100.00 | Using index condition |
        +----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
        1 row in set, 1 warning (0.00 sec)

    • index - The index join type is the same as ALL, except that the index tree is scanned.

      • CREATE AND INSERT
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        DROP TABLE IF EXISTS `user`;
        CREATE TABLE `user` (
        `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(16) NOT NULL,
        `email` VARCHAR(255) NULL,
        `password` VARCHAR(32) NOT NULL,
        `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        CREATE INDEX idx_user_email ON `user` (`email`);
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam2', 'xxx@gmail.com', '1234');
        INSERT INTO `user` (`username`, `email`, `password`)  VALUES('jam3', 'xxx@gmail.com', '1234');
      • SELECT
        1
        2
        3
        4
        5
        6
        7
        mysql> EXPLAIN SELECT COUNT(*) FROM user;
        +----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
        | id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
        +----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
        |  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_user_email | 768     | NULL |    3 |   100.00 | Using index |
        +----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
        1 row in set, 1 warning (0.00 sec)

    • ALL - A full table scan is done for each combination of rows from the previous tables.

  • raw - how much data were access this query
    Example:

    1. CREATE AND INSERT 

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      DROP TABLE IF EXISTS `event_log`;
      CREATE TABLE `event_log` (
      `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      `title` VARCHAR(50) NOT NULL,
      `message` TEXT NULL,
      `status_code` INT NOT NULL,
      `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE INDEX idx_event_log_status_code ON `event_log` (`status_code`);
      DROP PROCEDURE IF EXISTS loop_insert_event_log;
      DELIMITER //  
      CREATE PROCEDURE loop_insert_event_log()
      BEGIN
      DECLARE i INT DEFAULT 1; 
      WHILE (i <= 1000) DO
          INSERT INTO `event_log` (`title`, `message`, `status_code`) VALUES ( CONCAT('message', i), 'content', FLOOR(RAND()*5));
          SET i = i+1;
      END WHILE;
      END;
      //
      DELIMITER ;
      CALL loop_insert_event_log();
      DROP PROCEDURE IF EXISTS loop_insert_event_log;

    2. EXPLAIN SELECT 

      1
      2
      3
      4
      5
      6
      7
      EXPLAIN SELECT * FROM event_log WHERE status_code = 3;
      +----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
      | id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
      +----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
      |  1 | SIMPLE      | event_log | NULL       | ref  | idx_event_log_status_code | idx_event_log_status_code | 4       | const |   57 |   100.00 | NULL  |
      +----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
      1 row in set, 1 warning (0.00 sec)

    3. Now we drop the index:

      1
      DROP INDEX idx_event_log_status_code ON `event_log`;

    4. EXLAIN SELECT again

      You can see the rows number became bigger than the select result with index.

      1
      2
      3
      4
      5
      6
      7
      8
      mysql> EXPLAIN SELECT * FROM event_log WHERE status_code = 3;
      +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
      | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
      |  1 | SIMPLE      | event_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  851 |    10.00 | Using where |
      +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)

Reference

Posted on Disqus:

Declare Quorum queue with go language

Last article we describe how to build a RabbitMQ cluster, and this article we will show that declare quorum queue with go and do some simple test to quorum queue.

What we need?

  • library for RabbitMQ: github.com/streadway/amqp
  • GoLang
  • A RabbitMQ Cluster - You can use way last article described or use other way official website do.
Read more »

Posted on Edited on Disqus:

Build a RabbitMQ cluster including HAPorxy by docker-composer

This article is composed of this part:

  • Why do we need a RabbitMQ cluster?
  • How many ways we can build a RabbitMQ cluster and which way is this article used?
  • Some things we need to know before starting
  • Build RabbitMQ cluster through CLI command manually
  • Build RabbitMQ cluster through docker-composer
  • Add HAProxy as load balance.
  • Build by docker-compose
  • Completed docker-compose.yml and directory structure
  • Source code
Read more »