does the order of index columns matter when we have equality operators?

Question

Given that I have the tables

CREATE TABLE `replies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `repliable_id` bigint unsigned NOT NULL,
  `repliable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
)

CREATE TABLE `threads` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `category_id` bigint unsigned NOT NULL,
  `replies_count` bigint unsigned NOT NULL DEFAULT '0',
  `views` bigint unsigned NOT NULL DEFAULT '0',
  `pinned` tinyint(1) NOT NULL DEFAULT '0',
  `locked` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `threads_slug_unique` (`slug`),
  KEY `threads_user_id_foreign` (`user_id`),
  KEY `threads_category_id_foreign` (`category_id`),
  KEY `threads_created_at_index` (`created_at`),
  KEY `threads_updated_at_index` (`updated_at`),
  CONSTRAINT `threads_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `threads_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=927 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The repliable_type has 3 distinct values

select count(distinct (repliable_type)) from replies;

The repliable_id has 2002 distinct values

select count(distinct (repliable_id)) from replies;

I tried to execute the following query with different index combinations

select *, (select replies.id from replies
             where replies.repliable_type='App\\Models\\Thread'
               and repliable_id=threads.id
             order by replies.created_at DESC
             LIMIT 1) as reply_created_at
    from threads;

1. when I create a multicolumn index on replies with the columns (repliable_type, repliable_id)

alter table replies add index repliable_type_repliable_id
                (repliable_type,repliable_id);

The query above, does not even use the reoliable_type_repliable_id index and it takes forever to execute.

2. But when I create the index repliable_id_repliable_type ( change the order of the columns )

alter table replies
    add index repliable_id_repliable_type(repliable_id, repliable_type);

Then the query above uses the index and the query runs quite fast

I am really curious to understand why this happens.

I though that the order of the columns in an index doesn't matter when we have equality operators.

Is it because the repliable_id has more distinct values than the repliable_type and it narrows down the selection in the index ?

Or because the repliable_id is compared again the threads.id column, while the repliable_type is compared against a constant ?

Answer 1

This "composite" index should help:

replies:  INDEX(repliable_type, repliable_id, created_at)

(And DROP INDEX repliable_type_repliable_id since it is now in the way. This may be why you are not seeing an improvement.)

Since that one seems to be giving you trouble, DROP any indexes starting with reliable_id, then

ADD INDEX(reliable_id, reliable_type, created_at)

If that fails, please provide SHOW CREATE TABLE as it stands when trying it, plus EXPLAIN SELECT ...