Laravel – Join and OrderBy statement very slow, 77 seconds, despite indexes

I’m having performance issues with MySQL, despite having indexes when using both join and order by . The query is currently taking 77 seconds to complete.

The purpose of the query is to order the table alphabetically.

Background

I have a MySQL database that contains two InnoDB tables.

• report_urls; and
• urls

Each table contains 500,000 records. There is a one-to-one relationship between report_urls and url. Whereas there is a one-to-many relationship between url and report_urls.

The tables have the following structure:

• report_urls

  • id (Primary Key, char36, UUID, indexed)

  • url_id (char36, UUID, indexed)

• urls

  • id (Primary Key, char36, UUID, indexed)

  • title (varchar 255, indexed)

I need to join these tables and order the joined results by alphabetically by the title column.

The query

I am using Laravel and the Eloquent ORM, but the SQL statement that is generated is:

select
  *
from
  `report_urls`
  inner join `urls` as `urls_title_sort` on `report_urls`.`url_id` = `urls_title_sort`.`id`
where
  `report_id` = '8f6598f0-34d0-48b0-be8b-41c1b8f61ffd'
order by
  `urls_title_sort`.`title` asc
limit
  11 offset 0

MySQL Explain

Running the above query with the EXPLAIN command outputs the following:

Show CREATE

The output of SHOW CREATE TABLE report_urls; is as follows:

CREATE TABLE `report_urls` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `report_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `violation_count` int unsigned NOT NULL DEFAULT '0',
  `resolved_violation_count` int unsigned NOT NULL DEFAULT '0',
  `violation_minor` int unsigned NOT NULL DEFAULT '0',
  `violation_moderate` int unsigned NOT NULL DEFAULT '0',
  `violation_serious` int unsigned NOT NULL DEFAULT '0',
  `violation_critical` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `report_urls_url_id_foreign` (`url_id`),
  KEY `report_urls_report_id_created_at_index` (`report_id`,`created_at`),
  KEY `report_urls_report_id_index` (`report_id`),
  KEY `report_urls_violation_count_index` (`violation_count`),
  KEY `report_urls_resolved_violation_count_index` (`resolved_violation_count`),
  KEY `report_urls_violation_minor_index` (`violation_minor`),
  KEY `report_urls_violation_moderate_index` (`violation_moderate`),
  KEY `report_urls_violation_serious_index` (`violation_serious`),
  KEY `report_urls_violation_critical_index` (`violation_critical`),
  KEY `report_urls_created_at_index` (`created_at`),
  CONSTRAINT `report_urls_report_id_foreign` FOREIGN KEY (`report_id`) REFERENCES `reports` (`id`) ON DELETE CASCADE,
  CONSTRAINT `report_urls_url_id_foreign` FOREIGN KEY (`url_id`) REFERENCES `urls` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Question

I need to join these tables together and sort them alphabetically by title.

The query currently takes 77 seconds to execute. I was hoping that MySQL could use the title index, but it doesn’t seem to use the index.

Can anyone suggest any performance improvements?