MySQL is not using index on query plan with Order By and Limit

Question

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.

Answer 1

• How many indexes does MySQL use when running a query? (If it decides to do so)

As many as the engine thinks is necessary to serve the query efficiently. That'll depend and vary from one query to the next. Generally for each set of predicates against a table in a query, one index is used to locate the data of that table.

• Is it better to have a single index for each column that is filtered in where clause or use a composite index?

A composite index. It'll be most efficient for searching when locating the data for your WHERE clause. Less than optimal or additional operations will likely be needed to search a table with multiple individual single column indexes.

There's also less overhead for the composite index being maintained as the data of those fields change and the index needs to be updated. It's also most practical to manage. Generally you don't want too many indexes on a single table.

If you have multiple queries that share some common fields in their predicates (e.g. WHERE clauses), then it's usually ideal to create a composite index that covers that commonality. For example if query 1 filters on ColumnA, ColumnB, and ColumnC and query 2 filters on ColumnC only, then creating an index defined on (ColumnC, ColumnA, ColumnB) would be applicable to serve both queries efficiently.

Data is stored sorted in the order of which the columns are specified (left to right) in the index definition. Therefore for the composite index to be applicable to both example queries, all columns must be specified in a contiguous order that's accessible without skipping through a column, from left to right. I.e. ColumnC is accessible first, for the second query, and all three columns are used for the first query, so that index definition covers both queries appropriately. It also covers any future queries that filter on both ColumnC and ColumnA as well.

Answer 2

I recommend adding these composite indexes:

INDEX(deleted_at, bitcoins, id)
INDEX(bitcoins, deleted_at, created_at)

In each of these, it can at least filter on the first two columns. This may (or may not) be a significant benefit, depending on the selectivity of the columns.

The one ending in id may be used to avoid sorting (cf ORDER BY), but it cannot predict how many rows to look at before finding 50 (cf LIMIT) that otherwise match.

The one ending in created_at will probably be able to limit how much to scan, but it will need a 'sort'.

Neither EXPLAIN can say enough to give clues about what to do next. And, depending on the distribution of the data, a different index may be used at different times.

Almost never will MySQL use more than one INDEX in a single SELECT. The Optimizer will make a separate analysis each time it encounters a query.

Note that my suggestions start with columns tested with = (or IS NULL), then have one "range".