mysqli_fetch_array exceeds memory

Question

I a running a relatively simple query:

SELECT gb2_designs.indx,
 gb2_designs.response_required,
 gb2_designs.user_design_name,
 gb2_designs.submitted,
 DATE_FORMAT(gb2_designs.submitted_timestamp,'%c/%d/%y') AS stime,
 DATE_FORMAT(gb2_designs.promised_ship,'%c/%d/%y') AS ps,
 DATE_FORMAT(gb2_designs.updated,'%c/%d/%y') AS upd,
 DATE_FORMAT(gb2_designs.promised_ship,'%c/%d/%y') AS dp,
 DATE_FORMAT(gb2_designs.shipped,'%c/%d/%y') AS ds,
 order_accepted, order_completed,
 gb2_designs.status,
 gb2_users.first_name,
 gb2_users.last_name,
 gb2_users.email
 FROM gb2_designs, gb2_users
 WHERE 1 && gb2_designs.user_indx=gb2_users.indx && 
 response_required=1
 ORDER BY gb2_designs.updated DESC";

And then iterate over the rows with:

 while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
 {
 $indx = $row["indx"];
 $user_design_name = $row["user_design_name"];
 ...more stuff''
 }

There are 104,422 rows in gb2_users and 70,385 in gb2_designs. When run manually, or in a .php file on apache that does only this, 5 elements are returned which appears to be correct. I am running Mysql version 5.7.40

When I am running it in a larger script, it returns the 5 rows matching the criteria successfully, then when it does the 6th (which should return null for $row), the code properly returns numm for $row, but issues the following to stdout:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 4096 bytes) in /home/omittedforprivacy/public_html/svadmin/lookup.php on line 1376. Line 1376 is the mysqli_fetch_arra($query,MYSQLI_ASSOC). It appears to be getting everything but issuing that pesky memory error. Apache is running without a memory limit set.

Any ideas?

Answer 1

PHP has its own memory limit set by the memory_limit directive in php.ini. It defaults to 128MB, and your allocation failed at a max usage of 32MB, so it seems to have been reduced in your environment. It might be coming from a login class limit on the webserver user.

Don't forget that you need 2 copies of the data in memory -- one for the data from the database, and another for the PHP data structures you are populating.

If you are searching for criteria, add the critera to the query so the database doesn't waste resources sending you useless data.