how to solve 'array overflow', in either php or using MySQL?

Question

Before the question , How to using php to get unique word pair(string) and insert into mysql table should read first

for example: if we have dog cat pair , we will not see cat dog

As @pala_ suggestion here 's my code

 $sql= "INSERT INTO EM (source,target) VALUES ";

$res = array();
foreach($combine_words_array as $v1) {
  foreach($combine_words_array as $v2) {
    $t = array($v1, $v2);
    asort($t);
    if(!in_array($t, $res)){
      $res[] = $t;
      $sql.="('$t[0]','$t[1]'),";
     mysql_query(substr($sql,0,-1));    
  }
  }
}

and question appear, this array must very huge and MySQL insert stop at 540000 rows ,is any idea that could use array dynamic or together with MySQL code?

Answer 1

I still think that you should keep this logic in SQL as such:

SELECT t1.column AS source, t2.column AS target FROM input_table t1
INNER JOIN input_table t2 ON t1.column < t2.column

That should give you all of the unique pairings and should be faster than getting unique pair with in_array testing in PHP. if the data is already in PHP and not in MySQL you could insert it 540000 rows at the time into a temp table and then run something like above to get the pairs you are interested in. Databases are meant for set operation, PHP definitely isn't.

If you insist on building the array in the way that you are doing it and keeping it all in PHP memory you should run mysql_query(substr($sql,0,-1)); line only if you've hit your row limit or have ended the outer loop. At which point you could reset your $sql string to

$sql= "INSERT INTO EM (source,target) VALUES ";

again and start building the remaining portion as you have up until now. Rinse and repeat until you are done or PHP process is out of memory =)