dimanche 3 janvier 2016

efficiently find first number that do not exist in a table?

i have a table with about 528829 rows, it looks like

CREATE TABLE `ips` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `ip` INTEGER NOT NULL DEFAULT NULL,
  `scantime` INTEGER NULL DEFAULT NULL,
  `pingable` INTEGER NULL DEFAULT NULL
);

now i need to find the first number that do NOT exist in ip , starting from 0 and going up to 4294967295 (aka 0xFFFFFFFF ),

currently i just use

function isScanned($ip){
    static $isScannedStm=false;
    static $boundip=0;
    if($isScannedStm===false){
    global $db;
    $isScannedStm=$db->prepare('SELECT 1 FROM `ips` WHERE `ip` = :ip LIMIT 1');
    $isScannedStm->bindParam(':ip',$boundip,PDO::PARAM_INT);
    return isScanned($ip);
    }
    $boundip=$ip;
    $isScannedStm->execute();
    //var_dump($isScannedStm->fetch(PDO::FETCH_NUM));
    return !!($isScannedStm->fetch(PDO::FETCH_NUM));
}
//~~~
    while(isScanned($i)){
        ++$i;
    }

..it works, but with 528829 rows, it takes over 1 hour and 30 minutes on my Intel Atom C2750 @ 2.4GHz.. how can i find this value faster? preferably, much much faster?

Aucun commentaire:

Enregistrer un commentaire