Hello, I have a question about explain, index in mysql
Real Estate Table 47.240 Order of Service Table 1,887,421
SELECT
SQL_NO_CACHE
COUNT(*)
FROM
imoveis I
INNER JOIN situacao_cod_venda SCV ON SCV.flag_mostra_site = 1 AND SCV.id = I.situacao_codigo_venda
WHERE
NOT EXISTS
(
SELECT
id
FROM
ordem_atendimento_envio OAE
WHERE
I.data_alt_sit_ven = OAE.data_ultima_alteracao_situacao AND
OAE.id_imovel = I.id AND
OAE.id_ordem_atendimento = '69451'
)
When I create the index for id_imovel from the table of work order, explain me shows 9438 and the query takes 5 seconds.
When I create the index for service_id_id from the service order table, explain me shows 478 and the query takes 15 seconds
in the table of buildings in explain both returns 48253
When I create a composite index id_imovel, service_id_id, the respsta is instantaneous, there is no second of 0,412 miles of seconds, but explain shows 9438.
I would like to know why the index that got smaller was slower? What is the rule to check the lines checked ex: explain house rows 48253 table order rows 478
so rows checked is 48253 * 478 = 23,064,934 (would it be?)
What does sifinifica when ref returns null, or const
See the video below for a detailed explanation.
MySql Behavior, Makes No Sense (VIDEO ON VIMEO)
index with less record, slower.
Hugs