Use indexes in the WHERE of the UPDATE and DELETE

Question

Use indexes in the WHERE of the UPDATE and DELETE

Navigation

#1 by (0 votes)

0

How optimal is it to create an index for the fields used in the WHERE of the UPDATE and DELETE statements? I have researched on the subject and in general I find split opinions, in general the penalty is mentioned for all the sentences that are not SELECT when creating indexes due to the fact of having to update / create an index for each record, that I understand, without However, opinions are divided when speaking of the use of said index in the WHERE clause of both DELETE and UPDATE:

DELETE FROM mitabla
WHERE miCampoIndexado = 123;

UPDATE miTabla
SET column1 = value1
WHERE miCampoIndexado = 456;

Because in this particular case the search would be performed by an indexed field (Similar to the search in the SELECT), the INSERT would be harmed in any way since it does not use said clause.

mysql sql indices

asked by Julian David 16.10.2018 в 01:21

source

1 answer

Search with SQLite filter How to print the catch message, console, jquery?

score 0 · Answer 1

Although you create an index with the fields that you use in the WHERE, you do not know if the RDBMS will use them. Beyond the definition of the indices, its use or not, depends on the statistics that the sql engine performs on the query you are making. Evaluates the total cost and depending on that, decides whether to use indices or not. Additionally, that TODAY the database engine USE the index you have created, does not mean that tomorrow, within a month or within 6 months continue using it. The decision goes through the monitoring of the BD, and the analysis of the statistics for the queries you make.