Mysql multiple index question

Question

I have a table (users) with columns like

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

And I will need to query this table as follows:

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

My questions are that since the “uid” column has an index in order to get the best performance for the above query, do I also need to set another index in the “activated” column? This table (will be large) will have great access to the "INSERT", "UPDATE", and also "SELECT" statements.

As I learned from other sources, the indexes are opposite the INSERT and UPDATE statements, so if the index in the uid column is enough for the index above, I won’t need to set another index for the activated one for “insert and update performance”.

Answer 1

In any case, MySQL will only use one index per table, so an extra index will not help.

However, if you want truly optimal performance, define your index in both columns in this order: (for example, 1 index over 2 columns)

index_name (uid, activated)

This will optimize the search only uidor uid AND activated.

Answer 2

uid uid . uid, , .. uid = x , . uid = x uid = x, = 1 , .

, .

Answer 3

.

, .

, , , : .

The creation of this index will also improve UPDATEand DELETEconcurrency: all accounts (both activated and non-activated) without the index for the specified uidwill be blocked for the time UPDATEin InnoDB.

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