Posted by Toby A Inkster on 05/10/07 17:50

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) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Back to original message]