php vs mysql speed

[Ciaran]

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?

Cheers,
Ciarán

[Ciaran]

On May 10, 5:05 pm, David Gillen <[e-mail]:

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

[Boris Stumm]

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?

I'd guess that a good DBMS can do it faster than you. I do not know that
much about MySQL, but with a decent optimizer and some indexes, the DBMS
will usually be faster. At least if it is not a completely strange query :)

Maybe you could define "compley query" (post the query here?), and define
what you mean with "lots of simple queries".

[Boris Stumm]

Ciaran wrote:

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;

In terms of complexity, both queries are equally complex, although the
first one looks a bit like there is some design issue. Anyways, doing it
the first way will be lot faster than doing it the second way (in my
opinion)

I'm basically asking: Is it contacting the database that slows down
PHP or is it the actual speed at which mySQL runs.

Depends :)

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.

See my other post.

[Toby A Inkster]

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

[Chung Leong]

On May 10, 6:43 pm, Ciaran <[e-mail]:

On May 10, 5:05 pm, David Gillen <[e-mail]:

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-queriesmay 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.

[Ciaran]

On May 10, 6:50 pm, Toby A Inkster <[e-mail]>
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

[-Lost]

Ciaran wrote:

On May 10, 6:50 pm, Toby A Inkster <[e-mail]>
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.

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.

Um... the way you are wording that is bugging me (for lack of better words).

PHP and MySQL are not the same thing. Not in form or fashion. So
saying PHP is faster than MySQL *may* be true, but...

....recreate what MySQL does in PHP -- which would mean relying on flat
files; storing huge cookies; storing ridiculous amounts of data in the
session. The second and third option is completely moronic.

So, having PHP do what MySQL can do natively would mean that MySQL is
all of a sudden *MUCH* faster than PHP.

Does this make sense?

For example: form validation, user input filtering, handling sessions,
et cetera should be handled by PHP. If it can be done by MySQL, it
probably should be done by MySQL.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

[Ciaran]

On May 12, 8:08 pm, -Lost <[e-mail]:

Ciaran wrote:

On May 10, 6:50 pm, Toby A Inkster <[e-mail]>
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.

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.

Um... the way you are wording that is bugging me (for lack of better words).

PHP and MySQL are not the same thing. Not in form or fashion. So
saying PHP is faster than MySQL *may* be true, but...

...recreate what MySQL does in PHP -- which would mean relying on flat
files; storing huge cookies; storing ridiculous amounts of data in the
session. The second and third option is completely moronic.

So, having PHP do what MySQL can do natively would mean that MySQL is
all of a sudden *MUCH* faster than PHP.

Does this make sense?

For example: form validation, user input filtering, handling sessions,
et cetera should be handled by PHP. If it can be done by MySQL, it
probably should be done by MySQL.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.- Hide quoted text -

- Show quoted text -

obviously. I was looking for a general rule of thumb as to weather to
favour many simple queries run on a loop or one long but comprehesive
query to fetch the data I need.

[-Lost]

Ciaran wrote:

On May 12, 8:08 pm, -Lost <[e-mail]:

>Ciaran wrote:

>>On May 10, 6:50 pm, Toby A Inkster <[e-mail]>
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.
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.

Um... the way you are wording that is bugging me (for lack of better words).

PHP and MySQL are not the same thing. Not in form or fashion. So
saying PHP is faster than MySQL *may* be true, but...

...recreate what MySQL does in PHP -- which would mean relying on flat
files; storing huge cookies; storing ridiculous amounts of data in the
session. The second and third option is completely moronic.

So, having PHP do what MySQL can do natively would mean that MySQL is
all of a sudden *MUCH* faster than PHP.

Does this make sense?

For example: form validation, user input filtering, handling sessions,
et cetera should be handled by PHP. If it can be done by MySQL, it
probably should be done by MySQL.

obviously. I was looking for a general rule of thumb as to weather to
favour many simple queries run on a loop or one long but comprehesive
query to fetch the data I need.

Right, of course. Much like what Toby said, I would use consolidated,
efficient queries.

This of course also depends on the physical layout of the database, like
others have said. Normalization, indexes, et cetera all play a key part
in making the queries as fast as they can be.

For the record, I was going to look up some documentation on the matter.
My first search "make mysql faster" actually yielded several worthy
results. Using a similar search for "make queries faster" again,
yielded very relevant results.

So instead of paste a bunch of links, Google for those two terms.

The forums at MySQL.com had some noteworthy threads, and IBM had very
good articles.

Good luck on this, and if you find some *really* interesting articles on
the subject, keep us posted.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

[NC]

On May 10, 8:39 am, Ciaran <[e-mail]:

>
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. There is a fixed per-query overhead, so, other things
being equal, one query would be executed faster than several queries.
But other things are not necessarily equal. Sometimes (not very often
though), there may be be an awkward join or ORDER BY clause, which
could be streamlined or even eliminated if you split your query into
two...

Cheers,
NC

[NC]

On May 10, 9:43 am, Ciaran <[e-mail]:

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

The first approach would be much faster.

I'm basically asking: Is it contacting the database that slows
down PHP or is it the actual speed at which mySQL runs.

It depends on the query. For simple queries, the per-query overhead
is important, so one query is faster than several. For complex
queries, especially those that involve joins and sorting, splitting
one complex query into two simpler ones may be a viable option.

I'm just looking for a rule of thumb here

There is only one rule of thumb here: when in doubut, test it out...

Cheers,
NC

[Roman]

Ciaran wrote:

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?

Cheers,
Ciarán

I would vote for single complex query over multiple queries with
sub-results being processed by PHP.

Do not forget that SQL is a procedural language and you can also write
algorithms in it (I am not 100% sure about MySQL though). Any PHP to
MySQL interraction will have an interprocess overhead (on same server)
or network communication overhead (MySQL hosted on different server).

Roman

[Schraalhans Keukenmeester]

At Sun, 13 May 2007 23:10:14 -0400, Roman let his monkeys type:

Ciaran wrote:

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

Cheers,
Ciarán

I would vote for single complex query over multiple queries with
sub-results being processed by PHP.

Do not forget that SQL is a procedural language and you can also write
algorithms in it (I am not 100% sure about MySQL though). Any PHP to
MySQL interraction will have an interprocess overhead (on same server)
or network communication overhead (MySQL hosted on different server).

Roman

MySQL supports stored procedures from v5 upwards I believe.

While this thread has offered several good points the most important
point in its summary would seem to be: it depends.

As someone said:"when in doubt, find it out." Very true, but I'd advise to
be pragmatic about it. If the total (execution) time of the SQL-related
stuff is only a fraction of a scripts total running time (a profiler could
help, but without it's quite doable to time things yourself), ask yourself
if it's really worth it putting in a lot of time and effort in optimizing
it. The famous 80/20 rule applies. Especially if a script isn't called a
gazillion times a day it's perhaps not important at all.

There seems to be quite an intrest with many/some in optimizing every
splinter of code, just for the heck of it. I'm not saying this is a bad
thing. Just that often using the time spent on this would be much better
spent on general program and data structure design.

That said, it is of course just one person's opinion, based on global
observations. So if you disagree, don't feel personally offended. Glad to
hear alternative views on this.

Best!,
Sh.