Optimize table indexes in MySQL
P粉841870942
P粉841870942 2023-07-25 09:47:08
0
1
578
<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>
P粉841870942
P粉841870942

reply all(1)
P粉269847997

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.


Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template