MySql query tunning

Question

I have a MySQL sql query as showing below.

Using explain, I can see the table 'cl_tiss_consulta'(alias BBB) and table 'cl_tiss_sadt'(alias CCC) are using a Full Scan as showing the attached picture.

To that table(cl_tiss_consulta and cl_tiss_sadt), I already have an index 'ix_data_fat' on column 'data_fat'

How can I change this query this table to use the index?

// MySQL sql query

 SELECT 
        A.id AS histo_id,
        DATE_FORMAT(A.horaent, '%d/%m/%Y %H:%i') AS horaent,
        C.cpf,
        KK.nome AS nomeconv,
        LL.nome AS nomeplano,
        B.nome AS nomemed
    FROM
        cl_histo A
            JOIN
        cl_clientes C ON C.id = A.cliente_id
            JOIN
        cl_staff_nh B ON B.id = A.med_id
            LEFT JOIN
        cl_planos LL ON LL.id = A.plano_id
            LEFT JOIN
        cl_convenio KK ON KK.id = LL.conv_id
    WHERE
        A.horaent >= '2022-04-08'
            AND A.horaent < '2022-04-09'
            AND A.deleted <> 1
            AND NOT (EXISTS( SELECT 
                1
            FROM
                cl_tiss_consulta BBB
            WHERE
                BBB.cliente_id = A.cliente_id
                    AND BBB.data_fat >= DATE(A.horaent)
                    AND BBB.data_fat < DATE_ADD(DATE(A.horaent),
                    INTERVAL 1 DAY))
or 
EXISTS( SELECT 
                1
            FROM
                cl_tiss_sadt CCC
            WHERE
                 CCC.cliente_id = A.cliente_id
                    AND  CCC.data_fat >= DATE(A.horaent)
                    AND  CCC.data_fat < DATE_ADD(DATE(A.horaent),
                    INTERVAL 1 DAY))
)

Answer 1

You need multicolumn indexes on (cliente_id, data_fat) on those two tables. You filter for equality on the first column, and on a datetime range on the second, so the two-column BTREE index is perfect for the situation.