Why does mysql join strategy depend on this value?

Question

I have a simple select, involving a join:

 SELECT `user`.*, `group`.`alias` AS `group_alias`
 FROM `user` INNER JOIN `group` ON `user`.`group_id` = `group`.`id` 
 WHERE `user`.`state` = 1 
 AND `group`.`state` = 0
 ORDER BY `user`.`id` DESC LIMIT 5 OFFSET 0;

When i set group.state to 0 in the where clause, it uses a ref strategy for joins and takes around 0.001s:

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: group
 type: ref
possible_keys: PRIMARY,idx_group_state
 key: idx_group_state
 key_len: 1
 ref: const
 rows: 11
 Extra: Using temporary; Using filesort
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: user
 type: ref
possible_keys: idx_group_id,idx_user_group_id
 key: idx_group_id
 key_len: 4
 ref: db.group.id
 rows: 5608
 Extra: Using where

When I set it to 1 however, it uses a range strategy and takes a lot longer (~2 sec)

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: group
 type: range
possible_keys: PRIMARY,idx_group_state
 key: idx_group_state
 key_len: 1
 ref: NULL
 rows: 35
 Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: user
 type: ref
possible_keys: idx_group_id,idx_user_group_id
 key: idx_group_id
 key_len: 4
 ref: db.group.id
 rows: 5608
 Extra: Using where

I have an index on state in both tables, user and group.

How can I dig further into this?

Can I change the behavior somehow, that querys with state = 1 are faster than state = 0?

Interestingly, if I remove group.alias from the selected values, ref strategy is also used everywhere (even with state = 1), which also confuses me a lot.

Answer 1

SELECT state, COUNT(*) FROM `group` GROUP BY state;

I think you will see that one state has lots of rows; the other has very few rows. This is sufficient to trigger use versus non-use of INDEX(state).

Please also provide

 SHOW CREATE TABLE `group`;

(While you are at it, do the same for user.) we may need to discuss other issues.

• stena: Thanks for the offer. I will add this information soon, but I need to prepare a shareable version having the same characteristic. Would I be able to manually tell MySQL to use the index for 1, but for 0, even if 1 has more rows?