1

Some background:

I have a php program that does a lot of things with large data sets that I get every 15 minutes (about 10 million records each file every 15 minutes). I have a table on a mysql database with phone numbers (over 300 million rows) that I need to check with each row in my file and if that phone number from the mysql table is contained in the raw file record I need to know that so I can add it to my statistics record. So far I have tried to just do a sql call each time like:

select * from phone.table where number = '$phoneNumber';

Where $phoneNumber is the number in the raw record that I'm trying to compare. Then I check if the query brought back results and that is how I know if that record contained a phone number I need to check for.

That is me doing 10 million sql queries every 15 minutes and it is just too slow and too memory intensive. The second thing I tried was to just do the sql query once and store the results in an array and compare the raw record phone numbers that way. But a 300 million record array stored in memory was just too much as well.

I'm at a loss here and I can't seem to find a way to do it. Just to add a few things, yes I have to have the table stored in mysql and yes I have to do this with PHP (boss requires it being done in php).

GrumpyCrouton
  • 8,486
  • 7
  • 32
  • 71
MJCS
  • 139
  • 1
  • 11
  • a normal PHP array consume a lot off memory..a SplFixedArray (http://php.net/manual/en/class.splfixedarray.php) uses less memory... https://stackoverflow.com/questions/11827668/does-really-splfixedarray-perform-better-than-arrays – Raymond Nijland Oct 18 '17 at 13:44
  • What about doing it the other way? Importing the data into a temporary table and doing a select where you compare the temp table with the content in your phone number table – rypskar Oct 18 '17 at 13:54
  • This is just one part of a much larger program. All the other calculations I do are are a per record basis and I need this to be on a per record basis as well. I normalize the data on per record basis. – MJCS Oct 18 '17 at 15:36

0 Answers0