Is a For Each statement beneficial on this Left Join?

Is it possible to use a For Each statement on this query to conserve server memory?  If yes, How do I add the For Each statement to this query?

The primary download has over 100,000 records.  The options table has over 100,000 records.  My server has 16GB of Ram but this left join uses about 9GB of RAM.  I am joining Sku and ParentSku columns.


$query1 = $conn->query('SELECT * FROM `download`
			LEFT JOIN `options`
			USING (ParentSKU)');

    while ($row1 = $query1->fetch(PDO::FETCH_ASSOC))
    {
	
	echo $row1['ProductName']."&nbsp;&nbsp;&nbsp;".$row1['Choice']."<BR>";



    }

Open in new window

lawrence_devAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
arnoldConnect With a Mentor Commented:
what is the upload option? file in CSV format?
foreach is just another looping mechanism. you seem to be trying to limit the size of the response, one option could be to cycle through limiting the amount of data returned, based on an Id column in downloads.
i.e. only get 50 rows at a time.
i.e. add limit 0,50
then the next time you run the query, have 50,50
You have to know how many records you have.
i.e. just for purposes of illustration not sure whether the syntax is right
for ($i=0;$i<=21000;$i++) {
$query1 = $conn->query('SELECT * FROM `download`
			LEFT JOIN `options`
			USING (ParentSKU) limit '.$i*50.',50');

    while ($row1 = $query1->fetch(PDO::FETCH_ASSOC))
    {
	
	echo $row1['ProductName']."&nbsp;&nbsp;&nbsp;".$row1['Choice']."<BR>";



    }
} //close for loop

Open in new window


you can adjust the chunk size i.e. 100 rows change the multiplier and the row count to match. 0,100 100,100.
That should have less tax on memory, but would require many more queries.
If this is an actively updated set of information you might pull data that is shortly there after could be changed.

using a mysql cursor is another approach that might work
creating a temporary table with that data is another.

I think I asked, but in case I did not what options are available to you for import?

What access to the current data do you have?
if you have access on the command line, you could  query the data on the mysql server and write it out to a file.

select * from downloads left join options using  into outfile  additional parameters on formating the output i.e. a CSV file, with cells surrounded by "" if they have a comma as part of the data etc.

terminated by ........
0
 
arnoldCommented:
Do you need all the columns from both tables?
Why not just select the ProductName and the Choice from their respective tables EX. download.ProductName,options.choice which seems to be the only two that you are outputing?

This should reduce the amount of memory needed to retain the large output.
0
 
lawrence_devAuthor Commented:
Thanks Arnold!
I chose ProductName and Choice just to make sure I was getting data from both tables.

The download table has 26 columns and the options table has 4.  I need to extract all data and merge both tables to upload to my online store.

I was thinking that a For Each statement would echo 1 complete row at a time and then go to the next...  
 
I am open for ideas!!!  Thanks again for your help!
0
 
lawrence_devAuthor Commented:
Great Ideas!  Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.