MySQL: Add column to unique index or add new index?

Question

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).

is it good reason enough that then uniqueness on (userid, name) would not be enforced? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 22, 2015 at 10:44
Is the table MyISAM or InnoDB? And will you share the query? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 22, 2015 at 10:49
@YperSillyCubeᵀᴹ I think I may not have made this clear enough; the unique key would have an extra column, so it'll still have it's uniqueness enforced. The other option would be to keep the unique key but add an new index on all three columns. — Martijn
– Martijn, Commented Dec 22, 2015 at 11:02
UNIQUE (userid, name) is different than UNIQUE (userid, name, valueclass). The first does not allow two rows with same userid and same name. The second does allow that. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 22, 2015 at 11:06
If you need UNIQUE (userid, name) for its uniqueness constraint, then adding INDEX(userid, name, valueclass) could be useful for "covering" that query. Neither index is redundant. — Rick James
– Rick James, Commented Dec 23, 2015 at 1:04

jbrahy · Accepted Answer · 2016-01-15 17:59:45Z

For one particularly heavy select query, I need an index on (userid,name,valueclass).

This is a good reason to add an index. It would also be good to examine your query plan. BTW, underscores between words make variables more legible.

Are there any good reasons why I should NOT just add a column to the unique index I already have?

A unique index enforces uniqueness. If you have a need for the two column uniqueness then most likely adding a third column to that will compromise the integrity of your constraint. To assist your heavy select query the best option would be to add an index to (userid,name,valueclass) without the unique constraint.

Stack Exchange Network

MySQL: Add column to unique index or add new index?

1 Answer 1

Your Answer

Hot Network Questions

MySQL: Add column to unique index or add new index?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions