Best way to display mysql results? - memory optimization question


I am having a problem with my website breaking my shared hosting service’s memory limit of 90M, so I am looking for ways to trim down and optimize my memory usage. My site is slowing down (it happened suddenly yesterday) and my hosting service says the virtual private server may have to be put in use. I don’t want this because I don’t have money.

I have a script like this:

$result = mysql_query("...... some mysql query") or die(mysql_error());
while($row = mysql_fetch_array($result)){
//.... print out HTML and formatted tables and info for each row in mysql query....

Is this the correct way to do it or is there a much more efficient way of doing this? I’ve never had to think much about this or ask a question like this before because I’ve never had memory problems.

Also, let me know if there are any other good memory optimization resources out there.


Hi peppy,

Do you return large results sets and then loop through them using PHP? Would it be possible to ‘Page’ your results so you provide only limited number of results each time (like a search engine or even sitepoint multi-paged threads)?


Greetings Steve,

Basically, I have a pagination thing set up for many of my pages. After 25 items are displayed, you have to go on to page 2 and so on. The start and end limits are handled by the mysql query though. Once I fetch the array, I use that while {} loop to display the details for each item (or mysql row).

You’re getting the results one row at a time. Seems pretty optimal to me. It would be worse if you buffered everything into an array.

Try this:

  # before and after your mysql statements
  echo '<br /'.  memory_get_usage(1);
  echo '<br /'.  memory_get_peak_usage(1);

  $result = mysql_query("...... some mysql query") or die(mysql_error());