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.
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.
SELECT table3.policynumber AS id, CONCAT(table3.'names',' ',table3.lastname) AS nombre, @var1 := SUBSTRING(pay_amb.month FROM 1 FOR 4) AS anio, ( SELECT SUM(pay_amb.commission) 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