Optimize table indexes in MySQL

Question

I have the following table (tracking searches):

CREATE TABLE `searches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) unsigned NOT NULL,
  `query` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userId` int(10) unsigned NOT NULL,
  `connectionId` int(10) unsigned NOT NULL,
  `pluginId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key,deleted` (`publicId`,`deleted`),
  KEY `deleted,userId` (`deleted`,`userId`),
  KEY `deleted,connectionId,pluginId` (`deleted`,`connectionId`,`pluginId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Sometimes I run a query that returns all queries performed by a specific user. In this case I have an index with the following columns: deleted, userId.

Other times, I run queries that return all queries executed by a specific "connection" and "plugin". In this case I have an index with the following columns: deleted, connectionId, pluginId.

The indexes are growing significantly in size, so I'm trying to understand how to better optimize them.

Is there value in merging these two indexes into one? For example, it could contain the following columns: deleted,userId,connectionId,pluginId

I'm not sure if doing this will reduce the size of the index while still meeting the query needs.

Answer 1

You can use an index multiple times, but only from left to right and cannot skip any fields. For example: index (field A, field B, field C), then you can use this index to query field A, or query field A and field B, or query field A, field B and field C, but it cannot be used Access to field A and field C or field B and field C.

So this is not applicable to your query.