CODEWITHSUNDEEP
phparrays
ashkufaraz
ashkufaraz

Reputation: 5297

Best way to work with large array or large table

I have table arseh with 10,000,000 record.

I need select from this table more than 10,000.

For increase speed I select all records from database and fill in global array, and then search in global array

         Arseh Table
------------------------------
|BlockCode|ID|FromYear|ToYear|
-----------------------------
|01011001 |1 |1350    |1395  |
|01250110 |1 |1355    |1366  |
|01020201 |10|1395    |1395  |

$ListMyArseBooklet = array();

function myselectArsehPriceBooklet($Year, $BlockCode, $ID) {
    $Handler = new DB();
    global $ListMyArseBooklet;

    if (empty($ListMyArseBooklet)) {
        //select from data base all data to global variable 
        $table = "arseh";
        $fields = "*";
        $ListMyArseBooklet = $baseClass - > select($table, $fields);
        //====================== 
    }

    //search value in array
    foreach($ListMyArseBooklet as $key => $R) {
            if ($R["BlockCode"] == $BlockCode && $R["ID"] == $ID && $R["FromYear"] <= $Year && $R["ToYear"] >= $Year)
                return $R;
    }
    //=====================
}

I call this function more than 10,000.

Problem: search time is about 10 seconds (I checked it). Is there a way that decrease this time?

Upvotes: 0

Views: 117

Answers (1)

Barry
Barry

Reputation: 3318

Your time taken is long because you are selecting everything and then searching in PHP, which is really bad idea. Databases are built to search are return sets of records... which is what you should do... especially as you are returning one record only each time... something like this...

$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("
    SELECT * 
    FROM table 
    WHERE BlockCode = ? 
        AND ID = ?
        AND ? NOT BETWEEN FromYear AND ToYear
    LIMIT 1
")) {
    $stmt->bind_param('s',$BlockCode);
    $stmt->bind_param('i',$ID);
    $stmt->bind_param('i',$Year);
    $stmt->execute();

    //use the output

    $stmt->close();
}
$mysqli->close();

The reason it is slower as you have it is that the database must take all the records and send them over the pipe to PHP that has to hold/process them and then do the logic of the search to get the result. It's like taking all books in the library home and searching for one of them, instead of just taking the single book home from the library.

Also using prepared statements helps the system optimized the query for each subsequent time it is run, instead of looking at the query as new each time.

Lastly depending on the exact DBMS there are many indexes, caches and storage methods to investigate as well to speed up the database end of the operation to make returning that one record faster.

If you are only focused on the PHP side of things (which isn't clear in your question but hinted at) then you are going to want to investigate sorting algorithms and data structures. For example, if the BlockCode is the most important factor int eh search then create a binary tree using the BlockCode and you will see a performance gain in storing / searching the data that way... but still strongly suspect focusing on the database is best.

Upvotes: 1

Related Questions