What better MySql index [closed]

1

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

    
asked by anonymous 13.11.2016 / 15:36

0 answers