2

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.

enter image description here

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

enter image description here

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 ?

10
  • 1
    Can you also try with an index on (repliable_type, repliable_id, created_at) ? Oct 2 at 10:19
  • 1
    I am not near a pc to type a proper answer but your assumption "I though that the order of the columns in an index doesn't matter when we have equality operators" is correct. However in your specific query the two equality conditions are not similar. The first, replies.repliable_type='App\\Models\\Thread', is against a constant while the second, repliable_id=threads.id, is against a column on another table. Thus, you have a correlated subquery(something like a LEFT JOIN) and both indexes can be used but with different plans. Oct 2 at 10:26
  • 1
    Additionally, you have the ORDER BY .. LIMIT 1 on a 3rd column which complicates matters even more. Oct 2 at 10:28
  • @ypercubeᵀᴹ I have tried that, it doesn't change anything. As long as the index starts with the column 'repliable_type', the result is the same. The database is not using the multicolumn index, instead it uses the index single column index 'created_at'. Oct 2 at 10:30
  • 1
    No, I don't think that the 3 vs 2000 distinct values matters. Try adding the 3-column index I suggested and removing all others, to see if it is being used. Oct 2 at 11:33
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 ...

2
  • i've tried that combination. But again the database doesn't use that key. As long as the index starts with the column repliable_type it seems that the database ignores the index. Oct 3 at 8:40
  • @OrestisuRic - Hmmm... I added to my Answer.
    – Rick James
    Oct 3 at 14:31

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.