Connection Limit, Slow Query (MYSQL)

Asked

Viewed 47 times

0

I performed a select to return me about 70k lines, everything was working perfectly until I add the function "group_concat()", after adding this function the query enters timeout and can not return me any item.

Is there any way to optimize this select so that it gets faster and I can perform the search?

SELECT 
        UPPER(c.nome)                                                                                                                                   AS Name,
        UPPER(CASE
            WHEN c.sexo = "F" THEN "Sra."
            WHEN c.sexo = "M"  THEN "Sr."
        END)                                                                                                                                            AS Title,
        c.empresa                                                                                                                                       AS Company,
        c.observacoes                                                                                                                                   AS Description,
        CASE
            WHEN c.uf IN ("AC", "AL", "AP", "AM", "BA", "CE", "DF", "ES", "GO", "MA", "MT", "MS", "MG", 
                        "PA", "PB", "PR", "PE", "PI", "RJ", "RN", "RS", "RO", "RR", "SC", "SP", "SE", "TO") THEN "BRASIL"
            ELSE NULL
        END                                                                                                                                             AS Country,
        c.cep                                                                                                                                           AS Zip,
        c.cidade                                                                                                                                        AS City,
        c.uf                                                                                                                                            AS State,
        CONCAT(COALESCE(c.endereco, ' '), " - ", c.complemento_end, " - ", c.bairro)                                                                    AS Adress,
        c.email                                                                                                                                         AS Email,
        GROUP_CONCAT(CASE WHEN sm.id IN (1, 2, 3, 4, 5, 6, 12) THEN CONCAT(sm.url, "/", cm.conta) ELSE CONCAT(sm.url, cm.conta) END SEPARATOR ', ')     AS Website,
        c.telefones                                                                                                                                     AS Phonenumber,
        pt.descricao                                                                                                                                    AS Tags,
        ca.descricao                                                                                                                                    AS Profissão,
        c.rg_nro                                                                                                                                        AS RG,
        c.data_nascimento                                                                                                                               AS 'Data de Nascimento',
        UPPER(c.mothers_name)                                                                                                                           AS 'Nome da Mãe',
        c.sexo                                                                                                                                          AS Sexo,
        ms.descricao                                                                                                                                    AS 'Estado Civil',
        c.escolaridade                                                                                                                                  AS Escolaridade,
        c.renda_estimada                                                                                                                                AS 'Renda Estimada',
        c.risco                                                                                                                                         AS Risco,
        GROUP_CONCAT(it.descricao SEPARATOR ', ')                                                                                                       AS Interesses,
        c.cpf                                                                                                                                           AS CPF

FROM customers_people c
LEFT JOIN customers_social_media cm
ON c.id =  cm.customer_person_id
LEFT JOIN social_media sm
ON cm.social_media_id = sm.id
LEFT JOIN careers ca
ON c.career_id = ca.id
LEFT JOIN marital_status ms
ON c.marital_status_id = ms.id
LEFT JOIN customers_products cp
ON c.id = cp.customer_person_id
LEFT JOIN product pt
ON cp.product_id = pt.id
LEFT JOIN customers_interests ci
ON c.id = ci.customer_id
LEFT JOIN interests it
ON ci.interest_id = it.id
GROUP BY c.id

Thank you in advance!

  • It’s quite obvious that everything comes with hardware limitations, but a simple solution is to increase the timeout in that query: https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query

  • Ever tried to give a EXPLAIN select to see where it bursts?

  • 1

    After increasing the timeout I managed, after a few minutes rs, to return all the values I needed. Thank you very much!!

No answers

Browser other questions tagged

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