Speed up mysql query inside a while loop

Question

I have the following query:

while ($start_date <= $end_date) {

            $date_array[] = date('d/m/Y H:i:s', strtotime($start_date));

            $start_date_interval = date('Y-m-d H:i:s', strtotime($start_date) + $interval);
            echo date('d/m/Y H:i:s', strtotime($start_date)) . ' ------> ';


            $query = "SELECT COUNT(id) AS call_count FROM callsepaug "
                    . "WHERE (i_billable = 1 AND ((start_time >= '$start_date' AND start_time < '$start_date_interval') "
                    . "OR (start_time > '$start_date_past' AND start_time < '$start_date_interval' "
                    . "AND end_time >= '$start_date')));";


            $result = mysqli_query($connection, $query);

            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

            $data[] = $row['call_count'];
            echo $row["call_count"] . ' active calls.<br>';

            $start_date = date('Y-m-d H:i:s', strtotime($start_date) + $interval);
            // echo $start_date . '<br>';
        }

However this query is too slow. The database contains a lot of data. A query to check an interval of 15 mins of a start and end given time take around 45 seconds. How can I greatly improve the speed?

Supposing start_time and end_time are indexed, I would say the problem is probably the OR condition. Try using an union of two subqueries — Guillem Vicens
– Guillem Vicens, Commented Oct 28, 2013 at 15:54

rakimo · Accepted Answer · 2013-10-28 16:03:14Z

2

Its better to write all queries outside loop as it executes query for every iteration fetch all the records between start date and end date and process them i your loop

answered Oct 28, 2013 at 16:03

rakimo

896 bronze badges

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

1 Comment

Dan Bracuk Over a year ago

To all who think this should be a comment, remember that one needs a reputation of 50 to comment on other people's posts.

Collectives™ on Stack Overflow

Speed up mysql query inside a while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related