CODEWITHSUNDEEP
mysqlindexing
Peeyush Kushwaha
Peeyush Kushwaha

Reputation: 3623

two indices in one table, is second index which is used only in where clause worth it?

Lets say, I have the following mysql table :

CREATE TABLE player (
    id int(9) unsigned NOT NULL DEFAULT 0 ,
    score MEDIUMINT(8) unsigned NOT NULL DEFAULT 0,
    signupdate DATE NOT NULL,
    lastupdate DATE NOT NULL
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

Currently I have a primary key on id column. lastupdate column is updated everyday, and if its not updated it means that the player has deleted the account, this means the cardianlity of this column is very low. Also there is a relational table matches with feilds matchid , playerid and matchdate

Most my queries are like 

SELECT id,score,signupdate FROM player 
JOIN matches ON matches.playerid = player.id 
WHERE lastupdate = '{today}'

So 3 cases for indices come to my mind

  1. PRIMARY KEY on id
  2. PRIMARY KEY on id and an INDEX on lastupdate
  3. PRIMARY KEY on (id,lastupdate)

Which one would be the best??

Upvotes: 0

Views: 208

Answers (2)

hol
hol

Reputation: 8423

You should have an index on table matches column playerid and an index on table player column lastupdate.

As a very rough rule of thumb is that what you use in the WHERE and JOIN clause should have an index if it is a large table.

To get more information what index was used you can use the explain statement. Here is what it looks like. Notice the explain statement at the very end:

mysql> CREATE TABLE player (
    ->     id int(9) unsigned NOT NULL DEFAULT 0 ,
    ->     score MEDIUMINT(8) unsigned NOT NULL DEFAULT 0,
    ->     signupdate DATE NOT NULL,
    ->     lastupdate DATE NOT NULL
    -> ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> CREATE TABLE matches (
    ->     matchid int(9) unsigned NOT NULL DEFAULT 0 ,
    ->     playerid int(9) unsigned NOT NULL DEFAULT 0 ,
    ->     matchdate DATE NOT NULL
    -> ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

mysql> 
mysql> SELECT id,score,signupdate FROM player 
    -> JOIN matches ON matches.playerid = player.id 
    -> WHERE lastupdate = now()
    -> ;
Empty set (0.00 sec)

mysql> 
mysql> explain
    -> SELECT id,score,signupdate FROM player 
    -> JOIN matches ON matches.playerid = player.id 
    -> WHERE lastupdate = '{today}'
    -> ;
+----+-------------+---------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+---------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | player  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                    |
|  1 | SIMPLE      | matches | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Using join buffer |
+----+-------------+---------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> CREATE INDEX player_idx_1 
    -> ON player (id)
    -> ;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX matches_idx_1 
    -> ON matches (playerid)
    -> ;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> explain SELECT id,score,signupdate FROM player  JOIN matches ON matches.playerid = player.id  WHERE lastupdate = '{today}';
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
| id | select_type | table   | type | possible_keys | key           | key_len | ref             | rows | Extra       |
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | player  | ALL  | player_idx_1  | NULL          | NULL    | NULL            |    1 | Using where |
|  1 | SIMPLE      | matches | ref  | matches_idx_1 | matches_idx_1 | 4       | mysql.player.id |    1 | Using index |
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql>

add the index for lastupdate

mysql> CREATE INDEX player_idx_2 
    -> ON player (lastupdate)
    -> ;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain
    -> SELECT id,score,signupdate FROM player 
    -> JOIN matches ON matches.playerid = player.id 
    -> WHERE lastupdate = curdate()
    -> ;
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
| id | select_type | table   | type | possible_keys | key           | key_len | ref             | rows | Extra       |
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | player  | ref  | player_idx_2  | player_idx_2  | 3       | const           |    1 |             |
|  1 | SIMPLE      | matches | ref  | matches_idx_1 | matches_idx_1 | 4       | mysql.player.id |    1 | Using index |
+----+-------------+---------+------+---------------+---------------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)

mysql>

Upvotes: 1

Juan Pablo Rinaldi
Juan Pablo Rinaldi

Reputation: 3494

Definitely number 2. Primary key is used to uniquely identify a row, and the id attribute is enough for that, so you don't need option 3. And since most of your queries look like what you said, then having an index on lastupdate will definitely be useful to speed your queries.

Upvotes: 0

Related Questions