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 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]
>
