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]
