Problem with group by in mysql and slowness in the query


I have the following situation with a MYSQL query. What happens to me is that I have two tables, that I'm joining by LEFT JOIN to bring all the records that are in the first table that is table3 .

In the second table pay_amb where the id is stored, I have them repeated so I am using a GROUP BY to group it and thus avoid duplicate records when displaying my query.

What is happening to me, is that when I place the GROUP BY to my query it generates a time out because the maximum execution time of my script was exceeded. it's in 500 . I have tried removing the GROUP BY and the query runs without problem. It is worth noting that I have placed the index in the following fields.

  • table3.policynumber
  • pay_amb.p_number
  • pay_amb.commission
  • pay_amb.month
  • Here is the question to see if you can help me. Thank you.


    Note: I am driving between the two tables as an average of about   30000 records.

      table3.policynumber AS id, 
      CONCAT(table3.'names',' ',table3.lastname) AS nombre, 
      @var1 := SUBSTRING(pay_amb.month FROM 1 FOR 4) AS anio,
        FROM pay_amb 
        WHERE pay_amb.p_number = table3.policynumber 
          AND SUBSTRING(pay_amb.month FROM 1 FOR 4) = @var1 
          AND SUBSTRING(pay_amb.month FROM 6 FOR 2) = '01'
      ) AS ene
    FROM table3 
      LEFT JOIN pay_amb 
      ON table3.policynumber = pay_amb.p_number 
    GROUP BY pay_amb.p_number
    LIMIT 0, 10

    Here is the result of explain

    asked by Yoel Rodriguez 10.09.2018 в 17:10

    0 answers