Return to Question

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;

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

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

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;

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

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

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.

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 ?

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`),
)

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.

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.