2

Structure of my table

mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE `t_group_tag_relation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) NOT NULL,
`group_tag_id` int(11) NOT NULL,
`resource_id` int(11) NOT NULL,
`resource_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `group_tag_id_resource_id` (`group_tag_id`,`resource_id`),
KEY `domain_id` (`domain_id`,`group_tag_id`,`resource_type`,`resource_id`),
KEY `domain_id_resource_type` (`domain_id`,`resource_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1613462 DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

Explain plan of my query

mysql> explain select * from t_group_tag_relation WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2;
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| 1 | SIMPLE       | t_group_tag_relation | ref   | domain_id,domain_id_resource_type | domain_id | 4 | const | 28400 | Using where; Using index |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
1 row in set (0.57 sec)

It is using indexes already but performance is not good. How to improve performance?

2 Answers 2

4

I think your explain plan is doing a heavy index scan. What you might need is an index with those exact three columns.

Please create this index

ALTER TABLE t_group_tag_relation ADD INDEX
resource_id_domain_id_resource_type_index (resource_id,domain_id,resource_type);

and try your query again.

1

It's likely that the cardinality of the 'domain_id_resource_type' index is lower than that of 'domain_id'. You can try to skip the optimizer choice and declare the usage of 'domain_id_resource_type' instead of 'domain_id' with the USE INDEX hint:

SELECT * FROM t_group_tag_relation USE INDEX(domain_id_resource_type)
  WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2;

Note that USE INDEX is expected to be deprecated in future versions of MySQL, and can be replaced with INDEX as of MySQL version 8.0.20. (This does not apply to MariaDB.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.