2
I’m reading a lot about using indexes in Mysql. I noted that it is really necessary to know how to use it at the right time and in the appropriate columns in order to make the queries faster and not the other way around.
Now, imagine the following consultation:
select campoX
from tabela
where campoA = 1
and campoB = 2
and campoC = 3
order by campoM
In this case, I created an index composed of the columns of the clause where: campoA, campoB, campoC. (using this same order)
My question concerns the order by field. I must include the field at the end of this same index to make my index viable?
Taking advantage of the same reasoning, if I used a group by in this consultation, I should also include it in this article?
Good. While I don’t want to answer this directly (I’m not a Mysql expert), I believe the answer will be "depends". If the result of the filter in question leaves few results the index does not help much (and will add time every time you make one
insertorupdate). The index helps if you expect a large number of results to be ordered. The best way to find out what is best is to add the index and compare the performance with the version without index for your use cases.– Anthony Accioly
I also do not know if I answer because I am in the same @Anthonyaccioly, depends on the data can be interesting at the end or at the beginning.
– Maniero