|
|
Posted by Ciaran on 05/13/07 00:36
On May 12, 8:08 pm, -Lost <maventheextrawo...@techie.com> wrote:
> Ciaran wrote:
> > 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.
>
> > 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.
Navigation:
[Reply to this message]
|