Best Practices For Using MySQL Index

The table used in this case is a user subscription table. Generally this table keeps records of users' subscription details: which topic they subscribed, when they last viewed their topics subscribed, who last replied a topic and when, when a default subscription will be expired (not used currently). Formerly we  wanted to use multiple tables. That is, store user $uid's information in table mysubs_$t ($t = $uid%8). After some consideration of the problems may be brought in the future, I decided to use one table.

The first definition of the table is as follows:

CREATE TABLE `mysubs` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `uid` int(11) unsigned NOT NULL default '0' COMMENT 'User id',
   `subs_type` int(3) unsigned NOT NULL default '0' COMMENT 'Subs type',
   `subs_id` int(11) unsigned NOT NULL default '0' COMMENT 'The article id subscribed',
   `last_view` timestamp NOT NULL default CURRENT_TIMESTAMP 
        COMMENT 'Last time user viewed a topic',
   `count_reply` int(11) unsigned NOT NULL default '0' COMMENT 'Reply counter',
   `last_reply_uid` int(11) unsigned NOT NULL default '0' 
        COMMENT 'uid that last replied a subs',
   `last_reply` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'Last reply time',
   `expire` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'Subs expiration time',
   `status` tinyint(1) unsigned NOT NULL default '1' COMMENT 'Subs status: 0 - canceled',
   PRIMARY KEY  (`id`),
   KEY `uid` (`uid`),
   KEY `subs_id` (`subs_id`),
   KEY `count_reply` (`count_reply`),
   KEY `last_view` (`last_view`),
   KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User subscription records';

After a discussion with some mates, I changed the table definition with this one:

CREATE TABLE `mysubs` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `uid` int(11) unsigned NOT NULL default '0' COMMENT 'User id',
   `subs_type` int(3) unsigned NOT NULL default '0' COMMENT 'Subscription type',
   `subs_id` int(11) unsigned NOT NULL default '0' COMMENT 'The article id subscribed',
   `last_view` int(10) unsigned NOT NULL default '0'  COMMENT 'Last time user viewed a topic',
   `count_reply` int(11) unsigned NOT NULL default '0' COMMENT 'Reply counter',
   `last_reply_uid` int(11) unsigned NOT NULL default '0' 
        COMMENT 'uid that last replied a subs',
   `last_reply` int(10) unsigned NOT NULL default '0' COMMENT 'Last reply time',
   `expire` int(10) unsigned NOT NULL default '0' COMMENT 'Subs expiration time',
   `status` tinyint(1) unsigned NOT NULL default '1' COMMENT 'Subs status: 0 - canceled',
   PRIMARY KEY  (`id`),
   KEY `uid` (`uid`),
   UNIQUE `subs_id` (`subs_id`, `subs_type`, `uid`),
   KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User subscription records';

With this new definition, SHOW INDEX FROM `mysubs` result now is:

mysql> SHOW INDEX FROM `mysubs`;
+--------+------------+-----------+--------------+-------------+-----------+-------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality |
+--------+------------+-----------+--------------+-------------+-----------+-------------+
| mysubs |          0 | PRIMARY   |            1 | id          | A         |          32 |
| mysubs |          0 | target_id |            1 | subs_id     | A         |          32 |
| mysubs |          0 | target_id |            2 | subs_type   | A         |          32 |
| mysubs |          0 | target_id |            3 | uid         | A         |          32 |
| mysubs |          1 | user_id   |            1 | uid         | A         |          16 |
| mysubs |          1 | expire    |            1 | expire      | A         |           2 |
+--------+------------+-----------+--------------+-------------+-----------+-------------+

Continued:
 +----------+--------+------+------------+---------+
 | Sub_part | Packed | Null | Index_type | Comment |
 +----------+--------+------+------------+---------+
 |     NULL | NULL   |      | BTREE      |         |
 |     NULL | NULL   |      | BTREE      |         |
 |     NULL | NULL   |      | BTREE      |         |
 |     NULL | NULL   |      | BTREE      |         |
 |     NULL | NULL   |      | BTREE      |         |
 |     NULL | NULL   |      | BTREE      |         |
 +----------+--------+------+------------+---------+

With the differences in mind, let me show you the SQLs I am using:

SELECT * FROM `mysubs` 
   WHERE `uid` = $uid AND `count_reply`>0 AND `status`=1 ORDER BY `last_reply`;

SELECT * FROM `mysubs` 
   WHERE `subs_id` = $subsId AND `subs_type` = $subsType AND `uid` = $uid AND `status`=1;

INSERT INTO `mysubs` (`subs_id`, `subs_type`, `uid`, `last_view`) 
   VALUES ($subsId, $subsType, $uid, time()) ON DUPLICATE KEY UPDATE `status`=1;

SELECT COUNT(1) AS `total` FROM `mysubs` 
   WHERE `user_id`=$uid AND `count_reply`>0 AND `status`=1;

UPDATE `mysubs` SET `last_reply_uid`=$uid, `last_reply`=time(), `count_reply`=`count_reply`+1
   WHERE `subs_type`=$subsType AND `subs_id`=$subsId;

DELETE FROM `mysubs` WHERE `subs_type`=$subsType AND `subs_id`=$subsId;

DELETE FROM `mysubs` WHERE `subs_type`=$subsType AND `subs_id`=$subsId AND `uid`=$uid;

The reason I used a UNIQUE index is obvious: A user's subscription to a topic with a subsType should only be one record.

The order reason of the UNIQUE index is that the subs_id can limit the record results to the largest extent. And one WHERE condition better use one index.

The result is the second table definition.

There are some other aspects to consider:

• Index on most integer feilds
• Keep track of the cardinality. Put the columns with the highest cardinality as the first keys
• Keep a look at the development environment
• Join index to index, the join goes a lot faster

Any good idea? Welcome to share:  .