mysql multiple index question

Question

I have a table(users) with columns as

id INT AUTOINVREMENT PRIMARY
uid INT index
email CHAR(128) UNIQUE
activated TINYINT

And I'll need to query this table like this:

SELECT * FROM users WHERE uid = ? AND activated = 1

My questions is, since there's an index set on the 'uid' column, in order to get the best performance for the above query, do I need to set another index to the 'activated' column too? This table(would be a big one) will be heavily accessed by 'INSERT', 'UPDATE' statements as well as 'SELECT' ones.

As I've learned from other sources that indexes goes opposite to 'INSERT' and 'UPDATE' statements, so if the index on the uid column is enough for the query above I won't have to set another index for activated for 'insert & update's performance sake.

Answer 1

MySQL will only use 1 index per table anyway, so having an additional index will not help.

However, if you want really optimal performance, define your index on both columns in this order: (eg. 1 index across 2 columns)

index_name (uid, activated)

That will allow optimized lookups of just uid, or uid AND activated.

Answer 2

It depends upon your data distribution and the selectivity of uid versus the selectivity of uid and activated. If you have lots of unique values of uid and this would have high selectivity ie searching for uid = x only returns a few rows then including activated in the index would provide little value. Whereas if uid = x returns lots of rows and uid = x and activated = 1 returns few rows then there's value in the index.

It's hard to provide a specific answer without know the data distribution.

Answer 3

Creating the index won't make you selects more slow.

However, it will make them significantly faster only if your search for unlike events.

This index will only be useful if the majority of your accounts are activated and you search for not-activated ones, or the other way round: the majority of your accounts are non-activated and you search for activated ones.

Creating this index will also improve UPDATE and DELETE concurrency: without this index, all accounts (both activated and not-activated) for a given uid will be locked for the duration of UPDATE operation in InnoDB.

However, an additional index will of course hamper the DML performance.