CODEWITHSUNDEEP
mysqlsqlindexing
krezus
krezus

Reputation: 1451

What kind of index should be added to mysql table?

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
);
  
CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`message` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

These are mysql tables and here is the query.

select COUNT(1)  
from  posts inner 
join users on posts.user_id = users.id  
where email = '[email protected]' 
  and created_at > DATE(NOW() - INTERVAL 30 DAY);

What kind of index would you add to the posts table to make this query work efficiently?

Thanks for any suggestion.

Upvotes: 1

Views: 50

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

You already have an index on users.email because of the UNIQUE KEY. This will optimize the row selection for your condition on email.

The primary key users.id is implicitly part of the index, and this will be used to look up the matching rows in posts.

Then you need an index on posts so each respective users.id can locate the matching rows efficiently. The index should also have a second column on created_at for the range condition.

ALTER TABLE posts ADD INDEX (user_id, created_at);

You can see how this affects the EXPLAIN and see that indexes are used.

Before new index:

+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY,email | email | 1022    | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | posts | NULL       | ALL   | NULL          | NULL  | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------------+

The join on posts will be forced to do a table-scan.

After new index:

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY,email | email   | 1022    | const |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | posts | NULL       | range | user_id       | user_id | 8       | NULL  |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

The join to posts uses an index.

You might like my presentation How to Design Indexes, Really, or the video of me presenting it.

Upvotes: 1

Related Questions