Query optimization

Asked

Viewed 29 times

1

Hey there, guys! I have this query (Mysql) to count amount of visits on a Mê in a given period, however it takes more than 1 minute to run. How can I optimize it? Thanks in advance.

SELECT MONTH(V1_DATA) AS MES,
    YEAR(V1_DATA) AS ANO,
    (SELECT COUNT(*) FROM `TV1-VISITAS` WHERE
        (V1_PG LIKE '%download%'
        OR V1_PG LIKE '%home%'
        OR V1_PG LIKE '%empresa%'
        OR V1_PG LIKE '%onde_comprar%'
        OR V1_PG LIKE '%eventos%'
        OR V1_PG LIKE '%contato%'
        OR V1_PG LIKE '%produtos%'
        OR V1_PG LIKE '%representantes%')
        AND YEAR(V1_DATA) = ANO
        AND MONTH(V1_DATA) = MES) AS INSTITUCIONAL,

    (SELECT COUNT(*) FROM `TV1-VISITAS` WHERE V1_PG LIKE '%evento%' AND YEAR(V1_DATA) = ANO AND MONTH(V1_DATA) = MES) AS EVENTOS,

    (SELECT COUNT(*) FROM `TV1-VISITAS` WHERE V1_PG LIKE '%produto%' AND YEAR(V1_DATA) = ANO AND MONTH(V1_DATA) = MES) AS PRODUTOS,

    (SELECT COUNT(*) FROM `TV1-VISITAS` WHERE V1_PG LIKE '%download%' AND YEAR(V1_HORA) = ANO AND MONTH(V1_DATA) = MES) AS ARQUIVOS

    FROM `TV1-VISITAS` WHERE YEAR(V1_DATA) = YEAR(NOW()) GROUP BY MES ORDER BY MES
mysql query optimizing

  • Try trading the selects for CASE https://stackoverflow.com/questions/20320631/how-to-pivot-a-table-in-mysql-using-case-statements

1 answer

1

Something that would be interesting would be to have this information that you filter in the "like" in a separate field.

Subselects take as a basis the year and month of the main query, you can delete them and use a SUM with CASE to get the same result. Your appointment would look like this:

SELECT MONTH(V1_DATA) AS MES,
    YEAR(V1_DATA) AS ANO,
    SUM(CASE WHEN V1_PG LIKE '%download%'
    OR V1_PG LIKE '%home%' OR V1_PG LIKE '%empresa%' OR V1_PG LIKE '%onde_comprar%' OR V1_PG LIKE '%eventos%' OR V1_PG LIKE '%contato%' OR V1_PG LIKE '%produtos%' OR V1_PG LIKE '%representantes%' THEN 1 ELSE 0 END) AS INSTITUCIONAL,
    SUM(CASE WHEN V1_PG LIKE '%evento%' THEN 1 ELSE 0 END) AS EVENTOS,
    SUM(CASE WHEN V1_PG LIKE '%produto%' THEN 1 ELSE 0 END) AS PRODUTOS,
    SUM(CASE WHEN V1_PG LIKE '%download%' THEN 1 ELSE 0 END) AS ARQUIVOS
    FROM `TV1-VISITAS` WHERE YEAR(V1_DATA) = YEAR(NOW()) GROUP BY MES ORDER BY MES

  • Man, you’re a genius! The query returned exactly as it should return (as it did with the previous query) but now with a return time of 0.2 sec! Thank you!

  • The problem of the operator LIKE especially with the % at the beginning of the condition will force a BD to make a FULL SCAN in the table. One way to ensure a quick response is to define more specific filters in a field that contains indices in the table. For example, you mentioned about a certain period. In this sense, your query lacks a WHERE V1_DATA BETWEEN .... Even better if you can specify a filter by a foreign key ID (eg: ID_CLIENTE = 100).

Browser other questions tagged mysql query optimizing

You are not signed in. Login or sign up in order to post.