Speeding up SQL loop

Question

I have a nested loop in my PHP code that is taking a long time and wondered of there was a way to speed this up:

$sql = "SELECT * FROM table_1";
$result = mysqli_query($sql);
while($row = mysqli_fetch_array($result)){

    $sql2 = "
    SELECT count(*) 
FROM user_modules  
WHERE 
begin_ymdhi >= ".$date_from." AND 
begin_ymdhi <= ".$date_to." AND 
(
    completed_ymdhi IS NULL OR  
    completed_ymdhi = '' 
) AND 
user_id = ".$row['user_id']." AND 
task_id = ".$row['task_id']." AND 
    module_discon = 'N' 
    ";
}

The outer query gets 1000 rows, the inner has to count across 10,000 rows - the run-time is around 115 seconds ! Is there any way to improve this method, either using a different technique or combined SQL query?

LEFT JOIN + GROUP BY

zerkms
– zerkms

2014-06-03 00:44:51 +00:00
Commented Jun 3, 2014 at 0:44 — zerkms
– zerkms, Commented Jun 3, 2014 at 0:44

Barmar · Accepted Answer · 2014-06-03 02:16:29Z

2

Don't use nested queries, combine them into a single query with a join:

SELECT t1.*, COUNT(u.user_id) ct
FROM table_1 t1
LEFT JOIN user_modules AS u ON u.user_id = t1.user_id AND u.task_id = t1.task_id
    AND u.begin_ymdhi BETWEEN '$date_from' AND '$date_to'
    AND u.module_discon = 'N'
GROUP BY t1.user_id, t1.task_id

edited Jun 3, 2014 at 2:16

answered Jun 3, 2014 at 0:47

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Adamission · Accepted Answer · 2014-06-03 00:53:03Z

0

Are the task_id's unique? if so, the most straight forward would be something like:

 $sql2 = "
    SELECT count(task_id) AS TaskCount
FROM user_modules  
WHERE 
begin_ymdhi >= ".$date_from." AND 
begin_ymdhi <= ".$date_to." AND 
(
    completed_ymdhi IS NULL OR  
    completed_ymdhi = '' 
) AND module_discon = 'N'  
group by user_id
";

$result = mysqli_query($sql2);

answered Jun 3, 2014 at 0:53

Michael Adamission

4933 silver badges9 bronze badges

Comments

dened · Accepted Answer · 2014-06-03 02:18:20Z

0

SELECT user_modules.user_id, user_modules.task_id, count(*) 
FROM user_modules LEFT JOIN table_1 USING (user_id, task_id) 
WHERE  
begin_ymdhi >= ".$date_from." AND 
begin_ymdhi <= ".$date_to." AND 
    module_discon = 'N' AND
(
    completed_ymdhi IS NULL OR  
    completed_ymdhi = '' 
) 
GROUP BY user_modules.user_id, user_modules.task_id

Append EXPLAIN before that entire SELECT statement (i.e EXPLAIN SELECT count(*)...) and MySQL will give you a run-down on what the select is doing.

Make sure the begin_ymdhi field is indexed properly. SHOW INDEX FROM table_2 to see.

edited Jun 3, 2014 at 2:18

dened

4,32020 silver badges34 bronze badges

answered Jun 3, 2014 at 0:49

Andy Jones

6,2804 gold badges33 silver badges47 bronze badges

3 Comments

Barmar Over a year ago

ON should be USING when you write it like that.

Barmar Over a year ago

Don't you have the order of the join backwards? table1 should be the primary table, user_modules is the subordinate table.

Andy Jones Over a year ago

I think he's trying to get the count from user_modules, per user, no? Hmmm... I may be wrong on this

Collectives™ on Stack Overflow

Speeding up SQL loop

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related