Posted by Ciaran on 05/10/07 22:54

On May 10, 6:50 pm, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Ciaran wrote:
> > Is it faster to have mySql look up as much data as possible in one
> > complex query or to have php do all the complex processing and submit
> > lots of simple queries to the mysql database?
>
> It depends what you mean by "one big query".
>
> In general, slowest:
>
> SELECT * FROM people;
> (PHP: Loop through results to find Bob and Dave.)
> (PHP: Only print out the telephone numbers for each person,
> ignoring other columns in the table.)
>
> Slightly faster:
>
> SELECT phone_no FROM people WHERE name='Bob';
> (PHP: Print it.)
> SELECT phone_no FROM people WHERE name='Dave';
> (PHP: Print it.)
>
> Faster still:
>
> SELECT phone_no FROM people WHERE name IN ('Bob', 'Dave');
> (PHP: Loop through results, printing them.)
>
> Both the first and the third queries can be described as "one big query".
> One of them is the fastest method, the other is the slowest!
>
> In general, it's fastest if you do things in as few queries as possible,
> but making sure you're only ever fetching the exact data you need -- no
> more, no less.
>
> Narrow down the columns you're selecting to only the ones you need. Be
> vicious with your WHERE clause to make sure you don't select any
> additional rows. Whenever you're doing two SELECTs on related data,
> consider whether a single SELECT and a JOIN might be better.
>
> --
> Toby A Inkster BSc (Hons) ARCShttp://tobyinkster.co.uk/
> Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

Thanks a lot for the replies on this guys - I think I have the idea
now. It was bugging me because php is obviously faster than mysql so I
was wondering if it might be better to make it do the work. I guess it
depends on the exact query. I'm not doing anything too complex - just
pulling data from various tables so I suppose I'll stick to the mySQL
side for as much as I can.

Cheers,
Ciarán

[Back to original message]