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
– LipESprY
Ever tried to give a
EXPLAIN
select to see where it bursts?– fernandosavio
After increasing the timeout I managed, after a few minutes rs, to return all the values I needed. Thank you very much!!
– Gabriel Castro