Why does MySQL ignore index in this query?

Question

I have the following tables:

CREATE TABLE `sms` (
  `sms_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sms_datetime` datetime DEFAULT NULL,
  `sms_number` varchar(40) NOT NULL,
  `sms_text` text,
  `sms_status` int(3) DEFAULT '0',
  `sms_last_tm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sms_csinteve_id` int(11) unsigned NOT NULL DEFAULT '0',
  `sms_handler_user_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`sms_id`),
  KEY `sms_number` (`sms_number`),
  KEY `sms_last_tm` (`sms_last_tm`),
  KEY `sms_csinteve_id` (`sms_csinteve_id`),
  KEY `sms_handler_user_id` (`sms_handler_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

sms has more that 2,000,000 rows.

And the following table:

CREATE TABLE `customer_service_interaction_events` (
  `csinteve_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `csinteve_interaction_id` int(11) unsigned NOT NULL DEFAULT '0',
  `csinteve_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`csinteve_id`),
  KEY `csinteve_interaction_id` (`csinteve_interaction_id`),
  KEY `csinteve_datetime` (`csinteve_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=21153 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

with 20,000 rows.

Then this query:

EXPLAIN SELECT COUNT(csinteve_id) as interactions_number
FROM customer_service_interaction_events
JOIN sms
    ON csinteve_id = sms_csinteve_id
WHERE csinteve_interaction_id = 3085
AND sms_handler_user_id = 0;

says that:

{
    "data":
    [
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "customer_service_interaction_events",
            "partitions": null,
            "type": "ref",
            "possible_keys": "PRIMARY,csinteve_interaction_id",
            "key": "csinteve_interaction_id",
            "key_len": "4",
            "ref": "const",
            "rows": 1,
            "filtered": 100,
            "Extra": "Using index"
        },
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "sms",
            "partitions": null,
            "type": "ALL",
            "possible_keys": "sms_csinteve_id,sms_handler_user_id",
            "key": null,
            "key_len": null,
            "ref": null,
            "rows": 2083577,
            "filtered": 99.4,
            "Extra": "Using where; Using join buffer (Block Nested Loop)"
        }
    ]
}

says that for the sms table MySQL doesn't use any index (type ALL). Whereas, the sms table has an index on the sms_csinteve_id column which is in the join with the customer_service_interaction_events and that customer_service_interaction_events table has an index on csinteve_interaction_id.

The goal of the query is to return the count of unhandled SMSs (sms), each one is bound to a customer service event (customer_service_interaction_events) and several customer service events have the same interaction id (customer_service_interaction_events.csinteve_interaction_id, in the example 3085).

Thank you.

EDIT:

I tried to add Akina's indexes:

CREATE INDEX idx ON customer_service_interaction_events (csinteve_interaction_id, csinteve_id);
CREATE INDEX idx ON sms (sms_handler_user_id, sms_csinteve_id);

But the EXPLAIN:

EXPLAIN SELECT COUNT(csinteve_id) as interactions_number
FROM customer_service_interaction_events
JOIN sms
    ON csinteve_id = sms_csinteve_id
WHERE csinteve_interaction_id = 3085
AND sms_handler_user_id = 0;

Still outputs type ALL for the sms:

{
    "data":
    [
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "customer_service_interaction_events",
            "partitions": null,
            "type": "ref",
            "possible_keys": "PRIMARY,csinteve_interaction_id,csinteve_interaction_id__csinteve_id",
            "key": "csinteve_interaction_id",
            "key_len": "4",
            "ref": "const",
            "rows": 1,
            "filtered": 100,
            "Extra": "Using index"
        },
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "sms",
            "partitions": null,
            "type": "ref",
            "possible_keys": "sms_csinteve_id,sms_handler_user_id,sms_csinteve_id_2,sms_handler_user_id_2,sms_csinteve_id_3,sms_handler_user_id_3,sms_csinteve_id_4,sms_handler_user_id_4,sms_handler_user_id__sms_csinteve_id",
            "key": "sms_handler_user_id__sms_csinteve_id",
            "key_len": "8",
            "ref": "const,local_bluelinks.customer_service_interaction_events.csinteve_id",
            "rows": 2083577,
            "filtered": 100,
            "Extra": "Using index"
        }
    ]
}

Answer 1

Test this:

CREATE INDEX idx ON customer_service_interaction_events (csinteve_interaction_id, csinteve_id);
CREATE INDEX idx ON sms (sms_handler_user_id, sms_csinteve_id);