Home / Questions / 237684
Accepted
Yoel Rodriguez
Asked: 2020-02-14 12:02:16 +0800 CST 2020-02-14 12:02:16 +0800 CST

How can I improve the performance of my Query in mysql

  • 772

I have the following situation, I have a table where I am managing data of people, which I am saving by month and by type of insurer. The amount of data I'm saving for each person in a month can range from 1 to n . I have to place this data in a graph, so I need it to look like this:

January, February, March...December

Performing subquery I get them but it takes me a long time, the operations to perform. I have placed the indexes in the search and sum fields and even so it is very slow for me, it can take up to 15 minutes. In the table I am handling around 15,000 records. Then I leave the query to see if you can help me.

Thanks.

    SELECT @var2 := tabla1.id_insured AS id_insured, @var1 := tabla1.pyear AS anio,
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '01') AS ene, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '02') AS feb, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '03') AS mar, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '04') AS abr, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '05') AS may, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '06') AS jun, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '07') AS jul, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '08') AS ago, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '09') AS sep, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '10') AS octb, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '11') AS nov, 
    (SELECT SUM(tabla1.commission) FROM tabla1 WHERE tabla1.id_insured = @var2 AND tabla1.pyear = @var1 AND tabla1.month = '12') AS dic 
    FROM tabla1 
    WHERE tabla1.pyear = '2018'
    GROUP BY tabla1.id_insured
mysql

2 Answers

  1. Best Answer

    I think you are misreading the problem. As I understand you, what you are looking for is a table where you can see each insured and the sum of the commissions for each month of these, in 12 columns. A very simple way is using a conditional sum and grouping by the insured and the year (Eventually the year can go because you would be listing only one)

    SELECT  t.id_insured    AS id_insured,
        t.pyear         AS anio
        SUM(CASE WHEN t.month = '01' THEN t.commission ELSE 0 END) AS ene,
        SUM(CASE WHEN t.month = '02' THEN t.commission ELSE 0 END) AS feb,
        SUM(CASE WHEN t.month = '03' THEN t.commission ELSE 0 END) AS mar,
        SUM(CASE WHEN t.month = '04' THEN t.commission ELSE 0 END) AS abr,
        SUM(CASE WHEN t.month = '05' THEN t.commission ELSE 0 END) AS may,
        SUM(CASE WHEN t.month = '06' THEN t.commission ELSE 0 END) AS jun,
        SUM(CASE WHEN t.month = '07' THEN t.commission ELSE 0 END) AS jul,
        SUM(CASE WHEN t.month = '08' THEN t.commission ELSE 0 END) AS ago,
        SUM(CASE WHEN t.month = '09' THEN t.commission ELSE 0 END) AS sep,
        SUM(CASE WHEN t.month = '10' THEN t.commission ELSE 0 END) AS octb,
        SUM(CASE WHEN t.month = '11' THEN t.commission ELSE 0 END) AS nov,
        SUM(CASE WHEN t.month = '12' THEN t.commission ELSE 0 END) AS dic
        FROM tabla1 t
        WHERE t.pyear = '2018'
        GROUP BY t.id_insured,
                 t.pyear
    • 2

  2. A while ago I posted this question and there was no better performance of my Query until I found this explanation in a forum so I leave it here in case someone else has the same problem. The process that is carried out is to join the subconsults with join. Here is the example: 

    SELECT payid.id_insured, payid.c_name as nombre, payid.pyear as anio ,payid.id, ene.enecom as enecom ,feb.febcom as febcom,mar.marcom as marcom ,abr.abrcom as abrcom, may.maycom as maycom, jun.juncom as juncom, jul.julcom as julcom, ago.agocom as agocom, sep.sepcom as sepcom, oct.octcom as octcom, nov.novcom as novcom, dic.diccom as diccom
    FROM (SELECT tabla1.id_insured, tabla1.c_name, tabla1.pyear, tabla1.p_number AS id  FROM tabla1 GROUP BY tabla1.p_number ) AS payid LEFT JOIN 
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS enecom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '01' GROUP BY tabla1.p_number, tabla1.month) ene ON payid.id = ene.id LEFT JOIN 
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS febcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '02' GROUP BY tabla1.p_number, tabla1.month) feb ON payid.id = feb.id LEFT JOIN 
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS marcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '03' GROUP BY tabla1.p_number, tabla1.month) mar ON payid.id = mar.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS abrcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '04' GROUP BY tabla1.p_number, tabla1.month) abr ON payid.id = abr.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS maycom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '05' GROUP BY tabla1.p_number, tabla1.month) may ON payid.id = may.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS juncom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '06' GROUP BY tabla1.p_number, tabla1.month) jun ON payid.id = jun.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS julcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '07' GROUP BY tabla1.p_number, tabla1.month) jul ON payid.id = jul.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS agocom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '08' GROUP BY tabla1.p_number, tabla1.month) ago ON payid.id = ago.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS sepcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '09' GROUP BY tabla1.p_number, tabla1.month) sep ON payid.id = sep.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS octcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '10' GROUP BY tabla1.p_number, tabla1.month) oct ON payid.id = oct.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS novcom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '11' GROUP BY tabla1.p_number, tabla1.month) nov ON payid.id = nov.id LEFT JOIN
    (SELECT tabla1.p_number AS id, SUM(tabla1.commission) AS diccom FROM tabla1 WHERE  tabla1.pyear = '2019' AND tabla1.month = '12' GROUP BY tabla1.p_number, tabla1.month) dic ON payid.id = dic.id
    • 0