|
|
Posted by -Lost on 05/13/07 03:11
Ciaran wrote:
> 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.
>>
>>
>
> 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.
[Back to original message]
|