SQL Query Optimization in Mysql and Index

Question

Guys I’m having a performance problem at a consultation SQL in the MySQL who is using my server a lot, I’ve done index and yet consumption does not decrease. The query I’m using is:

SELECT
    CONCAT(
        '2015-10-14 ',
        Horas.Hora,
        ':00:00'
    ) AS HORA,
    COUNT(ID_CONTATO) AS TOTAL_GERAL
FROM
    (
        SELECT '00' Hora  UNION SELECT '01' UNION SELECT '02'
        UNION SELECT '03' UNION SELECT '04' UNION SELECT '05'
        UNION SELECT '06' UNION SELECT '07' UNION SELECT '08'
        UNION SELECT '09' UNION SELECT '10' UNION SELECT '11'
        UNION SELECT '12' UNION SELECT '13' UNION SELECT '14'
        UNION SELECT '15' UNION SELECT '16' UNION SELECT '17'
        UNION SELECT '18' UNION SELECT '19' UNION SELECT '20'
        UNION SELECT '21' UNION SELECT '22' UNION SELECT '23'
    ) Horas
LEFT JOIN CONTATO D ON SUBSTRING(D.CONTATO_DATA, 12, 2) = Horas.Hora
AND D.CONTATO_DATA LIKE '2015-10-14%'
AND CONTATO_CEP_BLOQUEADO IS NULL
GROUP BY
    CONCAT(
        '2015-10-14',
        Horas.Hora,
        ':00:00'
    )
ORDER BY 1

Have 325.000 registros in this table and this query is leading on average 7 segundos.

Fez index in all columns used and did not change anything the return time. If anyone knows any optimization I can do would be of great help. Thank you.

Follows the EXPLAIN of the result: http://sqlfiddle.com/#! 9/c034e/1/0

Answer 1

Your problem is at the points where Voce uses the function CONCAT and SUBSTRING. When you use this type of function as a search filter or grouping function, the Database cannot use the indexes for your queries and needs to do the TABLESCAN complete every time.

Adjust your query to not use these functions and you will see a significant performance improvement.

Answer 2

See if the query below helps.

select D.HORA, COUNT(D.idBalanceteVersao) AS TOTAL_GERAL from
(
    SELECT  '2015-10-14 '+ Horas.Hora +':00:00'  AS HORA, idBalanceteVersao
    FROM 
    (
        SELECT '00' as Hora UNION SELECT '01' UNION SELECT '02'
        UNION SELECT '03' UNION SELECT '04' UNION SELECT '05'
        UNION SELECT '06' UNION SELECT '07' UNION SELECT '08'
        UNION SELECT '09' UNION SELECT '10' UNION SELECT '11'
        UNION SELECT '12' UNION SELECT '13' UNION SELECT '14'
        UNION SELECT '15' UNION SELECT '16' UNION SELECT '17'
        UNION SELECT '18' UNION SELECT '19' UNION SELECT '20'
        UNION SELECT '21' UNION SELECT '22' UNION SELECT '23'
    ) Horas
    LEFT JOIN sgp.balancete_versao D 
    ON SUBSTRING(D.ultimaTransmissao, 12, 2) = Horas.Hora
    AND D.ultimaTransmissao LIKE '2015-10-14%'
    --
) D
GROUP BY D.HORA
ORDER BY 1