1

Problem:

I have a query that returns a large result set. It is too large to bring into PHP. I get a fatal memory max error and cannot increase memory limit. Unbuffered Queries

I need to iterate over the array multiple times but mysqli_data_seek doesn't work on unbuffered queries. mysqli_result::data_seek

//I have a buffered result set
$bresult = $mysql->query("SELECT * FROM Small_Table");

//And a very large unbuffered result set
$uresult = $mysqli->query("SELECT * FROM Big_Table", MYSQLI_USE_RESULT);

//The join to combine them takes too long and is too large
//The result set returned by the unbuffered query is too large itself to store in PHP
//There are too many rows in $bresult to re-execute the query or even a subset of it for each one

foreach($bresult as &$row) {
    //My solution was to search $uresult foreach row in $bresult to get the values I need
    $row['X'] = searchResult($uresult, $row['Key']);
    //PROBLEM: After the first search, $uresult is at its and and cannot be reset with mysqli_result::data_seek
}

function searchResult($uresult, $val)
    while($row = $uresult->fetch_assoc()){
        if($row['X'] == $val) {
            return $row['X'];
        }
    }
}

If you have another solution that meets these requirements I will accept it: - Does not try to join the result in a single query (takes too long) - Does not run any query for each result in another query (too many queries, takes too long, slows down system)

Please leave a comment if you need more info.

Thank you.

km6zla
  • 4,787
  • 2
  • 29
  • 51
  • Can I User `mysqli::real_query` followed by multiple calls to `mysqli::use_result`? – km6zla Aug 07 '13 at 18:33
  • `SELECT * FROM gigantic_table` is the kiss of death for any application. You will need to use `LIMIT` and `OFFSET` to get smaller chunks of data. Also try retrieving *only* the columns you absolutely need. – tadman Aug 07 '13 at 18:34
  • @tadman I am not actually doing that query. It is just example code. I am querying only the data required. – km6zla Aug 07 '13 at 18:37
  • Good to know. You'll need to `LIMIT` at the very least, though. If you're really in a jam, you can always dump the data to a plain-text file using `SELECT ... INTO OUTFILE` and parse that any way you want. – tadman Aug 07 '13 at 18:47

1 Answers1

0

If you're trying to process a big data set have you considered using an intermediary like Hadoop? you can set up a small hadoop cluster, do your processing, then have your php code make a request for the processed data to the hadoop output.

Zak
  • 24,947
  • 11
  • 38
  • 68
  • 3
    That's like suggesting using a nuclear bomb to dig a ditch in your backyard. – tadman Aug 07 '13 at 18:34
  • Thanks for the suggestion but it a little bit of overkill for this one small task. – km6zla Aug 07 '13 at 18:36
  • He's saying that the DATABASE is having a hard time joining his query. "//The join to combine them takes too long and is too large". That's an optimized compiled C program. And you think a little PHP will overcome a joining problem that he is saying is taking too long for the *database* !?! – Zak Aug 07 '13 at 18:57
  • There are ways to work around this by adjusting how you query the database. It's not an uncommon problem. – tadman Aug 07 '13 at 19:00
  • So I guess I should preface all of this by saying, make sure your joins are using indexed columns, instead of doing select * make sure you are only getting columns you need, etc... If you've looked at that and the join is still truly too large.. I don't think iterating over things in PHP will help speed anything up... – Zak Aug 07 '13 at 19:11