MySQL indexing tutorial for beginners

@tookapic

Story

I often encountered the problem because of an inappropriate indexing. It often happened at the site some newbie assigned. In fact, no indexing works well in the early days. But as time goes by; the database is to fat and delay the query response. So I wrote a tutorial for MySQL beginners to avoid the query problem.

create fat data

Let's create a test table.

mysql> use test;

mysql> CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL,
  `type` int(1) unsigned NOT NULL COMMENT 'type_id',
  `delete_fg` int(1) unsigned NOT NULL COMMENT '0: display, 1: delete',
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Product Table for test';

And create FAT test data.

mysql> create table a select 1 union select 2 union select 3 union select 4;
mysql> SET @id := 0;

mysql> insert into product select @id := @id + 1 id , 'xxx' name, type, round(rand() * 0.51) delete_flag from (select floor(rand() * 2 +1) type from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10) dat;

As a result like following. We successed in making more than a millon records.

mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.51 sec)

Using Explain

Next step, let's show the query plan. Just add EXPLAIN in front of your query like following.

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: type
          key: type
      key_len: 4
          ref: const
         rows: 523380
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

It looks like suspicion of extra, using where. We will try to ignore delete_fg.

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: type
          key: type
      key_len: 4
          ref: const
         rows: 523380
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

It changed to Using index from Using where. Let's investigate the queries differs.

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
count(*): 513419
1 row in set, 1 warning (2.71 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1'\G
*************************** 1. row ***************************
count(*): 523807
1 row in set, 1 warning (0.12 sec)

The difference is because of delete_fg is not indexing, so MySQL can't use the index in the query.

change the index to appropriate

Let's rebuild the index.

mysql> alter table product drop index type;
mysql> alter table product add index idx_type_delete_fg(type, delete_fg);

As a result. The query plan becomes fine.

mysql> EXPLAIN SELECT count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: product
   partitions: NULL
         type: ref
possible_keys: idx_type_delete_fg
          key: idx_type_delete_fg
      key_len: 8
          ref: const,const
         rows: 523380
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Let's get the records. It gets faster than before.

mysql> SELECT SQL_NO_CACHE count(*) FROM product WHERE type = '1' AND delete_fg = '0'\G
*************************** 1. row ***************************
count(*): 513419
1 row in set, 1 warning (0.12 sec)

recap

We often use MySQLand encounter some slow queries. In this case, the delete_fg is added by another Engineer after the app released.

At that time there was no problem, but it happens as time goes. There are some reasons for slow queries; At least we have to know how to fix inappropriate index.