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?

CodePudding user response：

For the query you show, you should create the index with state, another_id in that order.

Define the index with any columns referenced in equality conditions first, after them add one column referenced in a range condition or ORDER BY or GROUP BY.

You may also like my answer to Does Order of Fields of Multi-Column Index in MySQL Matter or my presentation How to Design Indexes, Really, or the video.

CodePudding user response：

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 <=