Posted by Rik on 12/12/06 02:12

Erwin Moller wrote:
> Cleverbum@hotmail.com wrote:
>
>> I currently have a list of md5 strings and need to check if a new
>> string is in that list hundreds of thousands of times. I've found
>> that the fastest way to do this is to have all the md5's stored in
>> an array and use the php function in_array().
>> my only problem now is that populating this array with data from my
>> sql server is rather slow, I currently use the lines:
>>
>> $resone = mysql_query("SELECT * FROM logs_full");
>> mysql_close();
>>
>> while ($row = mysql_fetch_array($resone)) {
>> $md5array[$md5count]= $row['textmd5'];
>> $md5count++;
>> }
>>
>> to do this. does anyone have a faster method?
>
> Hi,
>
> Loading the whole table into PHP's memory is surely slow.
> I think it is faster to make the field textmd5 UNIQUE on
> databaselevel, and just insert and of course catch the possible error
> (UNIQUE CONSTAINT VIOLATION etc etc): In case of collision it will
> complain. than handle that.
> In that way only your database has to scan the table, and will not
> tranfer its contents to php.

Indeed, or possibly check it first:
$searchstring = md5(//something);
$result = mysql_query("SELECT `textmd5` FROM `logs_full` WHERE `textmd5` =
'{$searchstring}'");
if(mysql_num_rows($result) < 1){
//not in table logic..
} else {
//is in table logic..
}

> Also, avoid * when making queries, it slows down too.
> You only want textmd5, so just ask for that field. :-)

Amen.
--
Rik Wasmus

[Back to original message]