Optimizing a table's index 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 queries to return all the queries done by a specific user. In that case, I have an index with the following columns: deleted,userId

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

The size of the indexes are growing substantially, and so I'm trying to understand how I could better optimize these.

Would there be value in combine both indexes into one? For example, it would have the following columns: deleted,userId,connectionId,pluginId

I'm not sure if this would result in a smaller index while fulfilling the queries in a acceptable with.

Answer 1

You can use an INDEX multiple times, but only from left to right without missing a field. Example: Index (fieldA,fieldB,fieldC) then you can use this for fieldA or fieldA and fieldB or fieldA,fieldB, fieldC but not for an access to fieldA and fieldC or fieldB and fieldC.

So this doesn't work for your queries