Should I begin a MySQL index with unique or non-unique field?

user_4486863 0
Viewed 51

The problem I have is the following:

  • I have a table that contains about 100000000 rows
  • it has 22 fields - some numeric, some text
  • it has a primary key id (auto-incremented integer)
  • it has a field another_id of type bigint, and a unique key on it
  • it has a field called state that can take only 4 integer values (0 to 3)
  • I need that the queries of the following form are executed as fast as possible:
SELECT COUNT(*) 
FROM my_table 
WHERE another_id IN ( <about 100 values> ) 
AND state = ...

for different values of state.

How should the index look like? I was thinking about two options:

  • KEY another_id:state (another_id, state)
  • KEY state:another_id (state, another_id)

Is there any difference in performance between those two variants? Is there anything else to consider?

Edit: engine is InnoDB

indexing query-optimization mysql sql
2 Answers
Bill_Karwin_208600

I agree with the answer above. One clarification though is that you want to have ita hash index not btree index. It should work faster. The hash index wouldn't work well with any queries that involve inequality such as <=

user_194894910
Related