Posted by Chung Leong on 05/10/07 21:37

On May 10, 6:43 pm, Ciaran <cronok...@hotmail.com> wrote:
> On May 10, 5:05 pm, David Gillen <Bel...@RedBrick.DCU.IE> wrote:
>
> > Ciaran said:> Hi All,
> > > 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 was vary. Sometime a complex multijoin query with nested sub-queries may be
> > quicker, other times it will take far longer.
> > Alot will depend on how your database is setup in terms of normalisation and
> > indexes on tables etc.
> > Without doing independent tests yourself for both cases it is impossible to
> > know.
>
> > D.
> > --
> > Fermat was right.
>
> Hmm, What about at the most basic level. For example would it be
> quicker to run:
> SELECT * FROM users WHERE userID IN
> (1,2,4,7,8,9,10,11,13,14,15,17,18,35,etc,etc,etc);
>
> or select each userID seperately in a PHP foreach loop with something
> like this statement:
> SELECT * FROM users WHERE userID=2 LIMIT 1;
>
> I'm basically asking: Is it contacting the database that slows down
> PHP or is it the actual speed at which mySQL runs.
>
> The former suggests that one big query would generally be quicker
> while the latter suggests many basic queries would generally be
> quicker.
> I'm just looking for a rule of thumb here, not hard answers.
>
> Cheers,
> Ciarán

In general one should minimize the number of queries, since they
incurr substantial overhead. For one thing, the RDMS needs time to
parse the SQL statement and work out a retrieval strategy. The RDMS
also wouldn't be able to find the optimal plan if you feed it the info
a bit at a time.

[Back to original message]