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"
        }
    ]
}

score -2 · Answer 1 · answered Nov 09 '21 at 08:25

-2

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);

answered Nov 09 '21 at 08:25

Akina

39,301
5
14
25

That didn't work, I still see `"rows": 1786395,` and `type: ALL` after adding the index you mention. – tonix Nov 09 '21 at 20:28
@tonix What percent of total table rows amount matches `csinteve_interaction_id = 3085`? `sms_handler_user_id = 0`? – Akina Nov 10 '21 at 05:07
Only a few records for which `csinteve_interaction_id = 3085` and `csinteve_id = sms_csinteve_id`, but there are many rows for which `sms_handler_user_id = 0`, the only thing is that I am only interested in the rows where `sms_handler_user_id = 0` but for which `csinteve_interaction_id = 3085` and `csinteve_id = sms_csinteve_id` 'cause these two conditions are the ones that I was thinking should narrow the query to a small set of rows to check. Any other idea on how I can boost this query and why does MySQL uses type `ALL` for `sms` even though I JOIN with `csinteve_id = sms_csinteve_id`? – tonix Nov 10 '21 at 22:19
@tonix *Only a few records for which csinteve_interaction_id = 3085* If so then the first index in my answer must be effective. *there are many rows for which sms_handler_user_id = 0* If so then 2nd index in my answer is not effective, and the index by `sms_csinteve_id` is suitable. – Akina Nov 11 '21 at 05:21
Yes, I guess so too, but even after adding these indexes, MySQL still doesn't use them for some reason. What could be the issue? – tonix Nov 11 '21 at 07:52
Please, check my EDIT. – tonix Nov 11 '21 at 07:56
1

@tonix Your plan shows that the server selects wrong tables scanning order. Try to swap the tables and use `FROM sms JOIN customer_service_interaction_events`. If the plan will not be altered then add `STRAIGHT_JOIN`. – Akina Nov 11 '21 at 08:14
Hi @Akina, thank you. Even if after adding the order of the tables and `STRAIGHT_JOIN` I still see `type: ALL` and `"rows": 2083577,` :'( – tonix Nov 13 '21 at 16:15

Why does MySQL ignore index in this query?

1 Answers1