I'm trying to understand the behaviour of the MySQL query planner where an index is not used when a query includes Order By and Limit combined.
The table in question is big with around 8 millons of rows. This is the DDL for it:
CREATE TABLE `contacts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`hash` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`first_name` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
`gender` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`birthdate` date DEFAULT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country_id` bigint(20) DEFAULT NULL,
`bitcoins` tinyint(1) DEFAULT '0',
`amount` int(11) DEFAULT NULL,
`term` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`cuit_cuil` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`document_type` tinyint(4) DEFAULT NULL,
`document_number` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`business_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`comment` text COLLATE utf8_unicode_ci,
`topic` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`bank_code` int(11) DEFAULT NULL,
`centralbank_verified` tinyint(1) NOT NULL DEFAULT '0',
`nosis_score` smallint(6) DEFAULT NULL,
`nosis_referencia_cantidad` tinyint(4) DEFAULT NULL,
`avg_delayed_days` smallint(6) DEFAULT NULL,
`nosis_titular_condicion` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
`origin` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`channel_slug` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_id` bigint(20) DEFAULT NULL,
`external_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`crm_id` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`credit_bureau` int(11) NOT NULL DEFAULT '0',
`max_credit_structure_id` bigint(20) unsigned DEFAULT NULL,
`extra_score_points` int(11) NOT NULL DEFAULT '0',
`reject_variable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`feedback` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`amount_offer` decimal(16,3) DEFAULT NULL,
`check_web` tinyint(1) NOT NULL DEFAULT '0',
`check_webhook` tinyint(1) NOT NULL DEFAULT '0',
`is_billable` tinyint(1) NOT NULL DEFAULT '0',
`billing_rejects` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`ip` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_agent` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`mkt_source` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`mkt_medium` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`mkt_campaign` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`mkt_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`initial_referrer` varchar(800) COLLATE utf8_unicode_ci DEFAULT NULL,
`initial_parameter` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
`own_supplier_id` int(10) unsigned DEFAULT NULL,
`reject_for_supplier` tinyint(1) NOT NULL DEFAULT '0',
`reject_kinds` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`operator_id` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
`creditrisk_engine_version_id` int(10) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `contacts_own_supplier_id_foreign` (`own_supplier_id`),
KEY `contacts_document_type_foreign` (`document_type`),
KEY `contacts_user_id_foreign` (`user_id`),
KEY `contacts_hash_index` (`hash`),
KEY `contacts_document_number_index` (`document_number`),
KEY `contacts_operator_id_foreign` (`operator_id`),
KEY `contacts_max_credit_structure_id_foreign` (`max_credit_structure_id`),
KEY `contacts_crm_id_index` (`crm_id`),
KEY `contacts_creditrisk_engine_version_id_foreign` (`creditrisk_engine_version_id`),
KEY `contacts_created_at_credit_bureau_index` (`created_at`,`credit_bureau`),
CONSTRAINT `contacts_creditrisk_engine_version_id_foreign` FOREIGN KEY (`creditrisk_engine_version_id`) REFERENCES `creditrisk_engine_versions` (`id`),
CONSTRAINT `contacts_document_type_foreign` FOREIGN KEY (`document_type`) REFERENCES `document_type` (`id`),
CONSTRAINT `contacts_max_credit_structure_id_foreign` FOREIGN KEY (`max_credit_structure_id`) REFERENCES `credit_structure` (`id`),
CONSTRAINT `contacts_operator_id_foreign` FOREIGN KEY (`operator_id`) REFERENCES `waynimovil_shared`.`users` (`id`),
CONSTRAINT `contacts_own_supplier_id_foreign` FOREIGN KEY (`own_supplier_id`) REFERENCES `suppliers` (`id`),
CONSTRAINT `contacts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7753802 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
This is the query that I need to run:
select `contacts`.*
from `contacts`
where `contacts`.`deleted_at` is null
and `contacts`.`bitcoins` = '0'
and `contacts`.`operator_id` is not null
and `contacts`.`created_at` >= '2023-02-24 02:42:17'
and `contacts`.`credit_bureau` > '0'
order by `id` desc limit 50
It takes around 15 seconds to finish, but if a remove order by and limit or use them separately, it comes down to 0.218 seconds.
This is the first explain for the full query:
╔══════╤═════════════╤══════════╤════════════╤═══════╤══════════════════════════════════════════════════════════════════════╤═════════╤═════════╤═════╤═══════╤══════════╤═════════════╗
║ # id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra ║
╠══════╪═════════════╪══════════╪════════════╪═══════╪══════════════════════════════════════════════════════════════════════╪═════════╪═════════╪═════╪═══════╪══════════╪═════════════╣
║ 1 │ SIMPLE │ contacts │ │ index │ contacts_operator_id_foreign,contacts_created_at_credit_bureau_index │ PRIMARY │ 8 │ │ 13478 │ 0.00 │ Using where ║
╚══════╧═════════════╧══════════╧════════════╧═══════╧══════════════════════════════════════════════════════════════════════╧═════════╧═════════╧═════╧═══════╧══════════╧═════════════╝
This is the second explain without order by and limit. If a I remove only remove order by, the result is the same and in case of limit, is almost the same with differences in the "Extra" column where "filesort" is added.
╔══════╤═════════════╤══════════╤════════════╤═══════╤══════════════════════════════════════════════════════════════════════╤═════════════════════════════════════════╤═════════╤═════╤═══════╤══════════╤════════════════════════════════════╗
║ # id │ select_type │ table │ partitions │ type │ possible_keys │ key │ key_len │ ref │ rows │ filtered │ Extra ║
╠══════╪═════════════╪══════════╪════════════╪═══════╪══════════════════════════════════════════════════════════════════════╪═════════════════════════════════════════╪═════════╪═════╪═══════╪══════════╪════════════════════════════════════╣
║ 1 │ SIMPLE │ contacts │ │ range │ contacts_operator_id_foreign,contacts_created_at_credit_bureau_index │ contacts_created_at_credit_bureau_index │ 8 │ │ 31490 │ 0.17 │ Using index condition; Using where ║
╚══════╧═════════════╧══════════╧════════════╧═══════╧══════════════════════════════════════════════════════════════════════╧═════════════════════════════════════════╧═════════╧═════╧═══════╧══════════╧════════════════════════════════════╝
I'm working with Performance Insights from AWS, dealing with the top resource demanding SQL queries and this is top 1. It's used very frequently in our processes.
I've been looking for answers and a way to solve this is to force MySQL to use "contacts_created_at_credit_bureau_index" key. I would like to avoid this approach or at least understand the reasons behind this. Since I'm not an SQL expert, I'm leaving some questions that you may find basic:
- How many indexes does MySQL use when running a query? (If it decides to do so)
- Is it better to have a single index for each column that is filtered in where clause or use a composite index?
Any advice or improvement would be really appreciated.
Thank you.