SQL Query Optimization in MySQL and Index

Question

SQL Query Optimization in MySQL and Index

Navigation

#1 by (4 votes)
#2 by (0 votes)

6

I have a performance problem with a query with SQL on MySQL that is using my server a lot, I have done index and even then the 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

I have 325.000 registros in this table and this query is averaging 7 segundos .

I made index on all the columns used and did not change the return time at all. If anyone knows any optimization I can do it would be a lot of help. Thank you.

Follow% w / w% of result: link

php mysql sql mysqli

asked by anonymous 14.10.2015 / 15:52

2 answers

Sorting with heapsort Insert data into an external Json

score 4 · Answer 1

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

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

score 0 · 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