Go Premium for a chance to win a PS4. Enter to Win


Is a For Each statement beneficial on this Left Join?

Posted on 2014-12-30
Medium Priority
Last Modified: 2014-12-30
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

Question by:lawrence_dev
  • 2
  • 2
LVL 80

Expert Comment

ID: 40524933
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.

Author Comment

ID: 40524944
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!
LVL 80

Accepted Solution

arnold earned 2000 total points
ID: 40524964
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 ........

Author Closing Comment

ID: 40524968
Great Ideas!  Thanks for your help!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question