CODEWITHSUNDEEP
phpmysqlsqlperformanceoptimization
user2203439
user2203439

Reputation:

Speed up mysql query inside a while loop

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?

Upvotes: 0

Views: 1203

Answers (1)

rakimo
rakimo

Reputation: 89

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

Upvotes: 2

Related Questions