Solved

Is a For Each statement beneficial on this Left Join?

Posted on 2014-12-30
4
79 Views
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

0
Comment
Question by:lawrence_dev
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:lawrence_dev
Comment Utility
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
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:lawrence_dev
Comment Utility
Great Ideas!  Thanks for your help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now