Optimize slow PHP database query performance

Data and Databases
1

I’m dealing with a performance issue in my PHP application. My code has two queries where the first one fetches data and then for each result I run another query to count records. This is really slow and I need help making it faster.

$query = "SELECT * FROM customers";
$data = mysqli_query($query);
while($item = mysqli_fetch_array($data)){

    $count_query = "
    SELECT count(*) 
    FROM activity_logs  
    WHERE 
    start_date >= ".$from_date." AND 
    start_date <= ".$to_date." AND 
    (
        end_date IS NULL OR  
        end_date = '' 
    ) AND 
    customer_id = ".$item['customer_id']." AND 
    product_id = ".$item['product_id']." AND 
    status = 'active' 
    ";
}

The first query returns about 1000 records and the counting query has to go through 10000 records each time. It takes almost 2 minutes to finish. Can anyone suggest a better approach or maybe a way to combine these into one query?

2

Wow, 2 minutes is brutal! Have you checked if there’s proper indexing on your activity_logs table? Also curious - why pull ALL customers first instead of filtering upfront? Maybe add WHERE conditions to that initial query to cut down those 1000 records?

3

I’ve hit the same performance issues. You’ve got an N+1 query problem - instead of running 1000+ separate queries, use a single JOIN or subquery. Here’s what fixed it for me: ```php
$query = “SELECT c., (SELECT COUNT() FROM activity_logs al WHERE al.start_date >= ‘$from_date’ AND al.start_date <= ‘$to_date’ AND (al.end_date IS NULL OR al.end_date = ‘’) AND al.customer_id = c.customer_id AND al.product_id = c.product_id AND al.status = ‘active’) as activity_count FROM customers c”;

This kills the loop and does everything in one database hit. Make sure you've got indexes on activity_logs for customer_id, product_id, start_date, and status. Took my query time from minutes to seconds.
4

Classic n+1 problem! Skip the loop and use LEFT JOIN with GROUP BY instead. Try SELECT c.*, COUNT(al.id) as log_count FROM customers c LEFT JOIN activity_logs al ON c.customer_id = al.customer_id AND c.product_id = al.product_id WHERE al.start_date BETWEEN '$from_date' AND '$to_date' AND al.status='active' GROUP BY c.customer_id - way faster than subqueries.