CODEWITHSUNDEEP
phpmysqlpdo
Krisztián Dudás
Krisztián Dudás

Reputation: 1016

Fastest query for processing high amount of data in mysql

I face a problem with a mysql query. The query runs but sometimes it makes php exceed the maximum memory. I have like 80 or 90.000 rows of coordinates, with engine speeds and other things. I have to create KML files to display routes individually. Where the engine speed is not null, the car is moving, if it is, the car is stopped. Half of the table's engine speeds contains 0s. When I'm iterating through the records, I also delete the records at the same time, after I created the routes array, but it runs very slowly and sometimes it runs out of memory. Can it be because of the high and massive data amount in the database or some logical error in my code? Here is the code:

public function getPositions($device_id) {
        $db = connect_database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT);
        $sql = "SELECT * FROM coordinates_log WHERE imei=:imei ORDER BY device_time ASC";
        $statement = $db->prepare($sql);
        $statement->execute(array(':imei' => $device_id));
        $positions = array();
        $delete_sql = "DELETE FROM coordinates_log WHERE id=:id";
        $delete_statement = $db->prepare($delete_sql);
        $counter = 0;
        $flag = 0;
        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            //here I flag the last started route
            if ($row['vehicle_engine_speed'] <= 0) {
                $flag = $counter;
            }
            $positions[] = $row;
            $counter++;
        }

        if (!empty($positions)) {
            $last_key = count($positions)-1;
            //here I check if the route is completed yet, or he is on his way
            if ($positions[$last_key]['vehicle_engine_speed'] != 0) {

                for($i = $flag; $i<=$last_key; $i++){
                    unset($positions[$i]);
                }
            }
            foreach ($positions as $position) {
                $delete_statement->execute(array(':id' => $position['id']));
            }
            return $positions;
        } else {
            return FALSE;
        }
    }

Upvotes: 0

Views: 249

Answers (2)

O. Jones
O. Jones

Reputation: 108839

The PDO subsystem in PHP offers two kinds of queries: buffered and unbuffered. Buffered queries are what you get if you don't specifically request unbuffered queries. Buffered queries consume more RAM in your PHP engine because PDO fetches the entire result set into RAM, then gives it back to your program a line at a time when you use $statement->fetch().

So, if your result sets are quite large and you can process them a row at a time, you will use less RAM with unbuffered mode. You process each row, then fetch the next one, without trying to hold them all in RAM at once.

Here's a writeup on unbuffered mode.

http://php.net/manual/en/mysqlinfo.concepts.buffering.php

Buffered mode is generally easier to use for programmers, because PDO reads the entire resultset from each query and implicitly closes the statement object. That leaves your connection available for the next sql statement, even if you have not yet processed all the information in your resultset. With unbuffered mode, if you want to run other mysql statements while you're processing your result set, you need another database connection to do that.

You should try unbuffered mode for your SELECT * FROM coordinates... result set.

Pro tip: If you avoid SELECT * and instead use SELECT col, col, col you probably can reduce the overhead of your queries, especially if you don't actually need all the columns.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 158005

Questions of the "Look at my code and tell me what's wrong with it" kind are off topic here. It is not only because the code is intended to be run, by the computers, not read by the humans, but because code itself is seldom relevant to the problem.

Before asking a question here, you have to profile your code, determining slowest parts, and memory consumption as well.

I could make some guesses though I hate it.

  • it could be query itself, if not optimized
  • it could be buffering issue, making whole resultset burden script's memory.
  • it could be select * issue, burdening your resulting array with lots of junk data
  • it could be slow writes due to innodb settings.

But guesswork doesn't make a good answer. You have to work out your question first.

Upvotes: 0

Related Questions