Reduce memory usage for big database calls php

46
March 14, 2019, at 9:50 PM

I am using this script to cross reference data so that I can retrieve the right data from the database which requires 3 checks of which 2 are run within a for loop

$state = str_replace("<span>","",$state);
                $state = str_replace("</span>","",$state);
                $getStateGroups = $wpdb->get_results("SELECT * FROM wp_gmw_locations WHERE region_code='$state'");
                $groupIdList = array();
                for($i = 0; $i < count($getStateGroups); $i++){
                    array_push($groupIdList,$getStateGroups[$i]->object_id);
                }
                $groupMemberList = array();
                for($i =0; $i < count($groupIdList);$i++){
                    $ggm = $wpdb->get_results("SELECT * FROM wp_bp_groups_members WHERE group_id=" . $groupIdList[$i]);
                    for($x = 0; $x < count($ggm); $i++){
                        array_push($groupMemberList,$ggm[$x]->user_id);
                    }
                }
                $whereList = implode(',',$groupMemberList);
                $userGameData = $wpdb->get_results("SELECT * FROM user_game_data WHERE uid IN ($whereList) ORDER BY lifetime_keys_spent DESC");
                return implode(',',$groupIdList);

using this I am running out of memory

I have tried increasing my memory limit from 256M to 3GB and it stills runs out of memory

I am using wordpress so $wpdb is the class that wordpress uses to interact with the database

I am required to cross reference the data pulled to get the information required for the functionality we want is there any way i can reduce the memory that this is trying to use or is there some bug that in the code that i am missing?

EDIT: The script runs out of memory on the line array_push($groupMemberList,$ggm[$x]->user_id);

Answer 1
SELECT * FROM wp_gmw_locations WHERE region_code='$state'

if you only need the ids, why are you selecting everything? (not to mention that just interpreting $state in there makes you prone for SQL injection attacks, read up on prepared statements.)

your first improvement would be only selecting the id:

SELECT object_id FROM wp_gmw_locations WHERE region_code='$state'

next thing: you're only using the ids for further queries, you're not using them in your code otherwise, right?
then why not let the database do the heavy lifting, it's much better at it:

SELECT * FROM wp_bp_groups_members WHERE group_id IN
  (SELECT object_id FROM wp_gmw_locations WHERE region_code='$state')

which not just gets rid of all that memory overhead, but also turns n+1 queries to the database into one.

again, you only need the id? further refine your select then!

SELECT user_id FROM wp_bp_groups_members WHERE group_id IN
  (SELECT object_id FROM wp_gmw_locations WHERE region_code='$state')

that should drastically reduce your memory footprint and your execution time. you should be able to take it from here and improve the rest of your code. i recommend spinning up a SQL-console and just playing around a bit so you get a feel for what the database can do for you - it's quite a lot! i haven't encountered any data retrieval task in the past few years i couldn't solve with just one query.

by the way: take a look at foreach-loops, they make code quite elegant compared to for-loops.

READ ALSO
Trying to Create WP Category Archive on Site the Uses Timber and AJAX Load More Plugin

Trying to Create WP Category Archive on Site the Uses Timber and AJAX Load More Plugin

I inherited a Wordpress website that uses the Timber templating engineI'm not overly familiar with Timber, but I've been able to learn enough to make simple edits to this point

12
How can I receive and parse my mails with php? [on hold]

How can I receive and parse my mails with php? [on hold]

I need to automate checking my emails and parsing information out of the headers with a PHP script, preferably in WordPressHow can I possibly do that? I am a bit of a noob, please help

29
curl semrush php API display_date

curl semrush php API display_date

i have this API request everything is working fine but two things thing when i add the display_date to the url the localhost and the hosting server give me this error efoodsdirectcom ERROR 612 :: Invalid display_date parameter, must be in format YYYYMM15...

22
Bypass Session Validations php

Bypass Session Validations php

Can Anyone bypass my Login page if they can bypass it how they well do it

15