Optimize table indexes in MySQL
P粉841870942
2023-07-25 09:47:08
<p>I have the following table (tracking searches): </p>
<pre class="brush:sql;toolbar:false;">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;
</pre>
<p>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. <code></code></p>
<p>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. <code></code></p>
<p>The indexes are growing significantly in size, so I'm trying to understand how to better optimize them. </p>
<p>Is there value in merging these two indexes into one? For example, it could contain the following columns: <code>deleted</code>,<code>userId</code>,<code>connectionId</code>,<code>pluginId</code></ p>
<p>I'm not sure if doing this will reduce the size of the index while still meeting the query needs. </p>
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.