For a MySQL database, I have an InnoDB table name user_metrics
with the following columns:
id INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
name VARCHAR(32) NOT NULL,
value DECIMAL(20,9) DEFAULT NULL
valueclass INT(11) DEFAULT NULL
For this table, I currently have a primary key (on id
) and a unique index on (userid,name)
. For one particularly heavy select
query, I need an index on (userid,name,valueclass)
.
I could create a new index or add the valueclass
column to the unique index I already have. As far as performance of the select
query is concerned, it doesn't matter. There also doesn't seem to be any significant difference in disk space or memory usage. Mutations to the table would be a bit slower, but it's essentially write-once-read-many.
Are there any good reasons why I should NOT just add a column to the unique index I already have?
The select
query has some joins as such:
SELECT `name`
, AVG(`valueclass`) AS `avg`
, STD(`valueclass`) AS `std`
FROM `user_metrics`
WHERE `valueclass` IS NOT NULL
AND `userid` IN ([list of userids])
AND `name` IN ([list of names we want])
GROUP BY `name`';
Basically, it tries to get some statistical information and only needs the three columns (so the entire query could be run on the index).
(userid, name)
would not be enforced? – ypercubeᵀᴹ Dec 22 '15 at 10:44UNIQUE (userid, name)
is different thanUNIQUE (userid, name, valueclass)
. The first does not allow two rows with sameuserid
and samename
. The second does allow that. – ypercubeᵀᴹ Dec 22 '15 at 11:06UNIQUE (userid, name)
for its uniqueness constraint, then addingINDEX(userid, name, valueclass)
could be useful for "covering" that query. Neither index is redundant. – Rick James Dec 23 '15 at 1:04