Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 79

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 79

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

661 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