0

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.

1 Answer 1

0
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.

1
  • 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?
    – stena
    Sep 17, 2021 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.