RE: What's Faster? MySQL Queries or PHP Loops?
I've been meaning to follow up on this post. Can either Peter or someone expand and provide an example of get all information in a single query... Thank you , Stuart --- Peter Lovatt [EMAIL PROTECTED] wrote: Hi What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's Faster? MySQL Queries or PHP Loops?
I assume he means selecting every entry in a given table (select column from table). You should be able to easily find the answer to this by inserting some code that records times as you run. My bets are on SQL. Cheers, Brian -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 10:49 AM To: Peter Lovatt; Brent Baisley; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: What's Faster? MySQL Queries or PHP Loops? I've been meaning to follow up on this post. Can either Peter or someone expand and provide an example of get all information in a single query... Thank you , Stuart --- Peter Lovatt [EMAIL PROTECTED] wrote: Hi What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
What's Faster? MySQL Queries or PHP Loops?
Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
You're talking about a difference of milliseconds, tops. Use whatever solution gives you the cleaner, easiest-to-maintain code. Don't worry about a couple milliseconds. http://c2.com/cgi/wiki?PrematureOptimization -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I'm confused about this response and am facing a similar situation. First, regarding the subject, what is the difference between a PHP or whatever loop and a SQL query. All the app code is doing is collecting the request and handing it back to the database. The DBMS still has to retrieve the data. My only guess is that the DBMS has to work a bit harder, as PHP would present the data in a cleaner / neater / parsed form back to the DBMS. Any of this make sense ? Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's Faster? MySQL Queries or PHP Loops?
Hi Just my two pence worth. Its not tested or scientific, and is based on gut feeling and experience. Connecting to mysql is resource intensive, particularly if you make a fresh connection for each query. A query will almost certainly involve disk acces which is slow. Sending the result of the query to php will also be resource intensive. In short the best approach is query efficiently as little as possible. What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
The end result will be the same, it's just a matter of the structure the data will have when handed to PHP for processing to display. It can be retrieved bit by bit and broken up into multiple lists or joined and summarized by MySQL into one list. One list will make the PHP loop simpler, smaller and faster. On Sep 8, 2004, at 2:12 PM, Stuart Felenstein wrote: I'm confused about this response and am facing a similar situation. First, regarding the subject, what is the difference between a PHP or whatever loop and a SQL query. All the app code is doing is collecting the request and handing it back to the database. The DBMS still has to retrieve the data. My only guess is that the DBMS has to work a bit harder, as PHP would present the data in a cleaner / neater / parsed form back to the DBMS. Any of this make sense ? Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I never thought of the return only the querying part. My predicament is I have a search form that queries a table with about 7 joins. It returns it via a Dreamweaver recordset aka SQL query. So based on what you said below , regarding the number of users, this is a bad way to go. Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: The end result will be the same, it's just a matter of the structure the data will have when handed to PHP for processing to display. It can be retrieved bit by bit and broken up into multiple lists or joined and summarized by MySQL into one list. One list will make the PHP loop simpler, smaller and faster. On Sep 8, 2004, at 2:12 PM, Stuart Felenstein wrote: I'm confused about this response and am facing a similar situation. First, regarding the subject, what is the difference between a PHP or whatever loop and a SQL query. All the app code is doing is collecting the request and handing it back to the database. The DBMS still has to retrieve the data. My only guess is that the DBMS has to work a bit harder, as PHP would present the data in a cleaner / neater / parsed form back to the DBMS. Any of this make sense ? Stuart --- Brent Baisley [EMAIL PROTECTED] wrote: I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server are on the same machine - an Apple Xserve. Second, I've got a page with a long repeat region reflecting a recordset queried out of a MySQL table. The region also displays information obtained from fields in a related table. Third, I use Dreamweaver to generate my MySQL recordsets and repeat regions. Here's the question... I can either A) in the header or my page, generate a recordset of all of the records in the related table and then loop through the recordset creating an array of the fields I need and then later pull from it in the repeat region... or B) take the six lines of code Dreamweaver generates to create a recordset and move them into the repeat region itself. In other words, I can create a recordset of all of the records in the related table, loop through it generating a PHP array and pull from this array later OR I can query the database every time through the loop while creating the repeat region. Since I haven't freed the table until the bottom of the page and because my MySQL Sever and PHP Server reside on the same machine, will I really notice a measurable difference in speed? If my MySQL Server were a different machine, I'm sure that there would be a noticable difference because all of the queries would be across a network (possibly the internet) and traffic would become a factor. Just wondering what other people have noticed. BTW, I've also posted this on the PHP board. Thanx -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's Faster? MySQL Queries or PHP Loops?
I use foxpro to do similar loops I've found that I get 10 queries per second on large tables, when connecting once, and issuing individual select statements via odbc. It is much faster if you can narrow the recordset into an array within php, and spool through that, unfortunatly I deal with 250+ million rows, so hitting the table by a index one at a time is faster Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's Faster? MySQL Queries or PHP Loops?
I agree with Peter. Hit the database only when necessary. You can even eliminate some JOINS entirely if you cache the contents of **certain** tables in application-side arrays However(!!) _*you*_ are now responsible for searching those arrays efficiently. Here's an example: create table states ( code char(2) not null, name varchar(30) not null ) create table users ( login varchar(18) not null, ... other user columns ... state_code char(2) ) If you are querying your users and want the full state name rather than the state's abbreviation, you would normally JOIN those two tables together in order to get the full state name. One option to the JOIN method is to preload the entire states table into an array and get the value from the array whenever your application needs it. This is only practical because the states table contains relatively static data. Frequently updated data would require frequent updates to your cache (array). Depending on the efficiency of your searching algorithm you may or may not save time. On the positive, doing it this way but you will shift some of the load from your database server to your application server. How much depends on how often you needed to make that JOIN. This means that even though this query didn't work faster, your database has a little more capacity left over to handle your more complex queries. No gain in one area but some gain somewhere else for a possible net positive. Repeating a query multiple times per page is normally _not_ a good idea. If you find yourself considering a process like that (like finding all of the news articles from a set of authors) where you first query to find your authors (you want all of the authors that were employed during March of 1999) then run sub-queries to find each article they wrote, don't do it. Save those extra trips to the database and just run one query that returns all of the data you will need to format your report. Then, use your application code (PHP) to present it in any way you like. SELECT w.name, w.agent, w.contactnumber, a.title, a.publishdate, a.text, a.publication FROM writers w INNER JOIN articles a ON a.writer_ID = w.ID WHERE w.startdate '1999-04-01' and (w.enddate = '1999-03-01' or w.enddate is null) Sure you will duplicate the information in the w columns for each article but the database time you save by running only one query and not multiples will be huge compared to the tiny bit of extra time you need in order to transfer the duplicated data. You could be in a situation where duplicating that data costs more time than it saves. However, in my experience, if you *are* in that situation you normally have larger problems to deal with than the speed of your data transfers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Peter Lovatt [EMAIL PROTECTED] wrote on 09/08/2004 02:15:44 PM: Hi Just my two pence worth. Its not tested or scientific, and is based on gut feeling and experience. Connecting to mysql is resource intensive, particularly if you make a fresh connection for each query. A query will almost certainly involve disk acces which is slow. Sending the result of the query to php will also be resource intensive. In short the best approach is query efficiently as little as possible. What I do is to try and get all information in a single query and then use php from there on. I go as far as building arrays from result sets and manipulating the data using php. Can't guarantee this is best practice but I have built big sites with big visitor numbers this way and they run OK :) HTH Peter -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 08 September 2004 19:01 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: What's Faster? MySQL Queries or PHP Loops? I would try not to query MySQL on each iteration of the loop. While a dozen or so queries may not make a noticeable difference, hundreds or thousands may. It's not a scalable technique, whether you need to scale it or not. Even if it's only 100 iterations, what if you have 10 people accessing the database at once? That's now 1,000 queries. You should try to have MySQL organize the data for you. Since you are using Dreamweaver to generate your code, your SQL knowledge may not be up to it. But there are a number of query options. Perhaps if you posted your table structure and the result you are looking for, the list could help with a query. Even though everything is on one machine, you still needed to do lots memory transfer from MySQL to Apache/PHP. The difference may not be noticeable, but I would always try to design for scalability. MySQL is designed to handle data so I would let it. On Sep 8, 2004, at 11:28 AM, Robb Kerr wrote: Here's the scenario... First, my HTTP Server (Apache), PHP Server and MySQL Server
