Memory usage exceeded on php / mysql_fetch_assoc and how to understand and fix it

Allowed memory size of 33554432 bytes exhausted error occurred and the line that is the killer is mysql_fetch_assoc and the files are very very large (many thousands of rows).

I am moving servers and the new one is a standard server while the older one on another host (who lost a backup on me) used as much resources as I needed as long as certain metrics were not breeched. Well, there is a lot less memory available, so I have to think outside the box. I’m getting the ‘out of memory’ error in php. I know it can be fixed with the likes of ini_set(“memory_limit”,”64M”), but lets see if we can be more efficient – I am thinking that the above code – wasn’t.

Two approaches are listed in this article to get around it

1. use an unbuffered command
2. get data in chunks (using limit command)

FINAL SOLUTION: The new place I work suggests to always put things in a sub-routine. Don’t always listen. In this case, I moved a query that returned a huge array into a loop that output the data as we went along row by row. Now we are OK. Memory issues averted – no need to change php.ini settings or anything. It loads faster too. Understand what the code does more than follow a rigourous recipe just because someone told you it was the rules (usually the people who write the rules are the ones that say that – in this case that is the case)