Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

Another test you can try... "probably" in your table the cardinality of 'domain_id_resource_type' index is lower than 'domain_id', you can try to skip the optimizer choice and declare the usage of 'domain_id_resource_type' instead of 'domain_id'

select * from t_group_tag_relation USE INDEX(domain_id_resource_type) WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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