Which is faster - MySQL query, or PHP loop

Jason Carlton wrote:
> In theory, which of these would be faster? Assume a few thousands
> rows:
>
> $query = "SELECT category FROM classifieds;
> $sth_classifieds = mysql_query($query);
>
> while (list($category) = mysql_fetch_array($sth_classifieds)) {
> if (isset($num_cats[$category])) $num_cats[$category]++;
> else $num_cats[$category] = 1;
> }
>
>
> Or:
>
> $query = "SELECT COUNT( category ) AS nums, category FROM classifieds
> GROUP BY category";
> $sth_classifieds = mysql_query($query);
>
> while (list($nums, $category) = mysql_fetch_array($sth_classifieds))
> $num_cats[$category] = $nums;
>
>
> The query itself is about 10 times faster for the first as for the
> second, but since the loop is a lot larger I'm not sure if the final
> result would be faster.

Your first query is fetching all rows from table classifieds and
counting them row by row; i.e. might as well use "SELECT COUNT(*) FROM
classifieds;"

The second is doing something entirely different.
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

Jason Carlton:


>With around 5,000 rows, and on a slow server, the first snippet takes
>0.0014 seconds to complete, while the second takes 0.013 seconds to
>complete.

Have you eliminated possible caching influences?

>During peak hours, though, I might have 800 requests/sec, so
>that difference can add up.

800 * 0.0014 = 1.12
800 * 0.013 = 10.4

Either way, you will not be able to process 800 requests per second.
Which website is this, anyway, please?


> I'm just not sure whether looping through
>5,000 rows in PHP would take long enough to offset the time gain of
>the query.

Why don't you just time that, then? I mean: you already have the
numbers from the MySql-variant, so why not collect the same numbers
from the PHP-side?
Get microtime(), run through your loop and get microtime() again.




--
Erick
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

Op 30-1-2010 5:47, Jason Carlton schreef:
> In theory, which of these would be faster? Assume a few thousands
> rows:
>
> $query = "SELECT category FROM classifieds;
> $sth_classifieds = mysql_query($query);
>
> while (list($category) = mysql_fetch_array($sth_classifieds)) {
> if (isset($num_cats[$category])) $num_cats[$category]++;
> else $num_cats[$category] = 1;
> }
>
>
> Or:
>
> $query = "SELECT COUNT( category ) AS nums, category FROM classifieds
> GROUP BY category";
> $sth_classifieds = mysql_query($query);
>
> while (list($nums, $category) = mysql_fetch_array($sth_classifieds))
> $num_cats[$category] = $nums;
>
>
> The query itself is about 10 times faster for the first as for the
> second, but since the loop is a lot larger I'm not sure if the final
> result would be faster.

And, i hope, there is an index on `category` ?

Your 1st query give more traffic between you MySQL-server and you
PHP/WEB-server, less load on MySQL, but more on PHP

The second gives less traffice, but more load on MySQL, and less on PHP

What is your setup? Do you have two physical servers, 1 database, and 1
web? Or is all on the same system?

--
Luuk
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

Jason Carlton wrote:
> On Jan 30, 3:24 am, Robert Hairgrove wrote:
>> Jason Carlton wrote:
>>> In theory, which of these would be faster? Assume a few thousands
>>> rows:
>>> $query = "SELECT category FROM classifieds;
>>> $sth_classifieds = mysql_query($query);
>>> while (list($category) = mysql_fetch_array($sth_classifieds)) {
>>> if (isset($num_cats[$category])) $num_cats[$category]++;
>>> else $num_cats[$category] = 1;
>>> }
>>> Or:
>>> $query = "SELECT COUNT( category ) AS nums, category FROM classifieds
>>> GROUP BY category";
>>> $sth_classifieds = mysql_query($query);
>>> while (list($nums, $category) = mysql_fetch_array($sth_classifieds))
>>> $num_cats[$category] = $nums;
>>> The query itself is about 10 times faster for the first as for the
>>> second, but since the loop is a lot larger I'm not sure if the final
>>> result would be faster.
>> Your first query is fetching all rows from table classifieds and
>> counting them row by row; i.e. might as well use "SELECT COUNT(*) FROM
>> classifieds;"
>>
>> The second is doing something entirely different.
>
> The result for both is actually the same. I'm counting the number of
> rows in each category, then assigning that number to $num_cats
> [$category] (where category is assigned previously in the script). The
> first snippet results in 5,000 rows to loop through, while the second
> results in 12.
>
> With around 5,000 rows, and on a slow server, the first snippet takes
> 0.0014 seconds to complete, while the second takes 0.013 seconds to
> complete. During peak hours, though, I might have 800 requests/sec, so
> that difference can add up. I'm just not sure whether looping through
> 5,000 rows in PHP would take long enough to offset the time gain of
> the query.

Probably not.

Also beware of memory usage.

I have PHP code that downsizes images to thumbnails to speed up
transfers to slow bandwidth clients. It worked brilliantly, until I gave
it a massive image to process. It wasn't juts N times slower, in was
hugely slower. Many seconds. I suspect it ran into PHP memory limits and
swapped..

Its probably worth using php to do work the database does badly, but I'd
never use it to do what the database does well..
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

The Natural Philosopher wrote:
> Jason Carlton wrote:
>> On Jan 30, 3:24 am, Robert Hairgrove wrote:
>>> Jason Carlton wrote:
>>>> In theory, which of these would be faster? Assume a few thousands
>>>> rows:
>>>> $query = "SELECT category FROM classifieds;
>>>> $sth_classifieds = mysql_query($query);
>>>> while (list($category) = mysql_fetch_array($sth_classifieds)) {
>>>> if (isset($num_cats[$category])) $num_cats[$category]++;
>>>> else $num_cats[$category] = 1;
>>>> }
>>>> Or:
>>>> $query = "SELECT COUNT( category ) AS nums, category FROM classifieds
>>>> GROUP BY category";
>>>> $sth_classifieds = mysql_query($query);
>>>> while (list($nums, $category) = mysql_fetch_array($sth_classifieds))
>>>> $num_cats[$category] = $nums;
>>>> The query itself is about 10 times faster for the first as for the
>>>> second, but since the loop is a lot larger I'm not sure if the final
>>>> result would be faster.
>>> Your first query is fetching all rows from table classifieds and
>>> counting them row by row; i.e. might as well use "SELECT COUNT(*) FROM
>>> classifieds;"
>>>
>>> The second is doing something entirely different.
>>
>> The result for both is actually the same. I'm counting the number of
>> rows in each category, then assigning that number to $num_cats
>> [$category] (where category is assigned previously in the script). The
>> first snippet results in 5,000 rows to loop through, while the second
>> results in 12.
>>
>> With around 5,000 rows, and on a slow server, the first snippet takes
>> 0.0014 seconds to complete, while the second takes 0.013 seconds to
>> complete. During peak hours, though, I might have 800 requests/sec, so
>> that difference can add up. I'm just not sure whether looping through
>> 5,000 rows in PHP would take long enough to offset the time gain of
>> the query.
>
> Probably not.
>
> Also beware of memory usage.
>
> I have PHP code that downsizes images to thumbnails to speed up
> transfers to slow bandwidth clients. It worked brilliantly, until I gave
> it a massive image to process. It wasn't juts N times slower, in was
> hugely slower. Many seconds. I suspect it ran into PHP memory limits and
> swapped..

If it hits the php memory limit, it will stop executing the script.

Swapping will happen depending on your swappiness setting, the amount ram you
have and how you have configured your web service (it's quite easy to
configure it to use more memory than you have on your machine and will slow
down things).

I have been working with similar things myself, where images where downsized
to proper size for cellphone screens, the trick is really to have a script
that generates the most common of those thumbnails, but let the displaying
script to have the possibility to regenerate the thumbnail if it would get
lost. Otherwise you have extreme load on the server after you add a lot of new
images to the system.


> Its probably worth using php to do work the database does badly, but I'd
> never use it to do what the database does well..

When doing the same query many times, it's worth to think about using
memcached to store the result, just see to remove the cached data when you
make an update.


--

//Aho
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

J.O. Aho wrote:
> The Natural Philosopher wrote:
>> Jason Carlton wrote:
>>> On Jan 30, 3:24 am, Robert Hairgrove wrote:
>>>> Jason Carlton wrote:
>>>>> In theory, which of these would be faster? Assume a few thousands
>>>>> rows:
>>>>> $query = "SELECT category FROM classifieds;
>>>>> $sth_classifieds = mysql_query($query);
>>>>> while (list($category) = mysql_fetch_array($sth_classifieds)) {
>>>>> if (isset($num_cats[$category])) $num_cats[$category]++;
>>>>> else $num_cats[$category] = 1;
>>>>> }
>>>>> Or:
>>>>> $query = "SELECT COUNT( category ) AS nums, category FROM classifieds
>>>>> GROUP BY category";
>>>>> $sth_classifieds = mysql_query($query);
>>>>> while (list($nums, $category) = mysql_fetch_array($sth_classifieds))
>>>>> $num_cats[$category] = $nums;
>>>>> The query itself is about 10 times faster for the first as for the
>>>>> second, but since the loop is a lot larger I'm not sure if the final
>>>>> result would be faster.
>>>> Your first query is fetching all rows from table classifieds and
>>>> counting them row by row; i.e. might as well use "SELECT COUNT(*) FROM
>>>> classifieds;"
>>>>
>>>> The second is doing something entirely different.
>>> The result for both is actually the same. I'm counting the number of
>>> rows in each category, then assigning that number to $num_cats
>>> [$category] (where category is assigned previously in the script). The
>>> first snippet results in 5,000 rows to loop through, while the second
>>> results in 12.
>>>
>>> With around 5,000 rows, and on a slow server, the first snippet takes
>>> 0.0014 seconds to complete, while the second takes 0.013 seconds to
>>> complete. During peak hours, though, I might have 800 requests/sec, so
>>> that difference can add up. I'm just not sure whether looping through
>>> 5,000 rows in PHP would take long enough to offset the time gain of
>>> the query.
>> Probably not.
>>
>> Also beware of memory usage.
>>
>> I have PHP code that downsizes images to thumbnails to speed up
>> transfers to slow bandwidth clients. It worked brilliantly, until I gave
>> it a massive image to process. It wasn't juts N times slower, in was
>> hugely slower. Many seconds. I suspect it ran into PHP memory limits and
>> swapped..
>
> If it hits the php memory limit, it will stop executing the script.
>

You are probably right: what is most likely happening is that Linux
needs too make extra RAM available to PHP, and has to flush some buffers.


> Swapping will happen depending on your swappiness setting, the amount ram you
> have and how you have configured your web service (it's quite easy to
> configure it to use more memory than you have on your machine and will slow
> down things).
>
> I have been working with similar things myself, where images where downsized
> to proper size for cellphone screens, the trick is really to have a script
> that generates the most common of those thumbnails, but let the displaying
> script to have the possibility to regenerate the thumbnail if it would get
> lost. Otherwise you have extreme load on the server after you add a lot of new
> images to the system.
>
Oh no, these are done on the fly every time a query is done on that image.

I have traded CPU power for bandwidth. At the scale of the site, its
more optimal.

If the site settles down, i might then rescale and store the thumbnails.
but right now they are used at three different sizes at least, and its
getting a pain to store different thumbnails at all sizes.


>
>> Its probably worth using php to do work the database does badly, but I'd
>> never use it to do what the database does well..
>
> When doing the same query many times, it's worth to think about using
> memcached to store the result, just see to remove the cached data when you
> make an update.
>
>

SQL does that anyway, all by itself.Makes the second identical 10 second
query happen in a flash!

Until you change a record...
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

The Natural Philosopher wrote:
> J.O. Aho wrote:
>> The Natural Philosopher wrote:

>> Swapping will happen depending on your swappiness setting, the amount
>> ram you
>> have and how you have configured your web service (it's quite easy to
>> configure it to use more memory than you have on your machine and will
>> slow
>> down things).
>>
>> I have been working with similar things myself, where images where
>> downsized
>> to proper size for cellphone screens, the trick is really to have a
>> script
>> that generates the most common of those thumbnails, but let the
>> displaying
>> script to have the possibility to regenerate the thumbnail if it would
>> get
>> lost. Otherwise you have extreme load on the server after you add a
>> lot of new
>> images to the system.
>>
> Oh no, these are done on the fly every time a query is done on that image.
> I have traded CPU power for bandwidth. At the scale of the site, its
> more optimal.

Thats just crazy, why generate a new thumbnail of an image if you already have
med it once, save the thumbnail and you save loads of CPU cycles.



> If the site settles down, i might then rescale and store the thumbnails.
> but right now they are used at three different sizes at least, and its
> getting a pain to store different thumbnails at all sizes.

On the site I worked on, for each image there was 5 different w/h ratio images
which had been cut from the original image and each of those images where
rescaled to the right size depending on the cellphones screen size.
As there are a lot of different cellphones with different screen resolutions,
there was quite many different thumbnails for the same image.

If image hadn't been auto generated ahead, the "web page" generated it, but
then went everything quite slowly and high CPU load, as for you. Of course we
didn't generate thumbnails for the uncommon screen resolutions, as we could
save storage space that way. In your case I think you only have one thumbnail
for each image, which will not be that much storage space needing.


>>> Its probably worth using php to do work the database does badly, but I'd
>>> never use it to do what the database does well..
>>
>> When doing the same query many times, it's worth to think about using
>> memcached to store the result, just see to remove the cached data when
>> you
>> make an update.
>>
> SQL does that anyway, all by itself.Makes the second identical 10 second
> query happen in a flash!
>
> Until you change a record...
>

MySql has some caching features, put those are far short time, if you have a
busy site, mysql may not have a heavy query cashed anymore a hour later, while
using memcached it wouldn't be a problem.

--

//Aho
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

J.O. Aho wrote:
> The Natural Philosopher wrote:
>> J.O. Aho wrote:
>>> The Natural Philosopher wrote:
>
>>> Swapping will happen depending on your swappiness setting, the amount
>>> ram you
>>> have and how you have configured your web service (it's quite easy to
>>> configure it to use more memory than you have on your machine and will
>>> slow
>>> down things).
>>>
>>> I have been working with similar things myself, where images where
>>> downsized
>>> to proper size for cellphone screens, the trick is really to have a
>>> script
>>> that generates the most common of those thumbnails, but let the
>>> displaying
>>> script to have the possibility to regenerate the thumbnail if it would
>>> get
>>> lost. Otherwise you have extreme load on the server after you add a
>>> lot of new
>>> images to the system.
>>>
>> Oh no, these are done on the fly every time a query is done on that image.
>> I have traded CPU power for bandwidth. At the scale of the site, its
>> more optimal.
>
> Thats just crazy, why generate a new thumbnail of an image if you already have
> med it once, save the thumbnail and you save loads of CPU cycles.
>

To save storage.

CPU is more than adequate. Backing up the storage is not.

>
>
>> If the site settles down, i might then rescale and store the thumbnails.
>> but right now they are used at three different sizes at least, and its
>> getting a pain to store different thumbnails at all sizes.
>
> On the site I worked on, for each image there was 5 different w/h ratio images
> which had been cut from the original image and each of those images where
> rescaled to the right size depending on the cellphones screen size.
> As there are a lot of different cellphones with different screen resolutions,
> there was quite many different thumbnails for the same image.
>
> If image hadn't been auto generated ahead, the "web page" generated it, but
> then went everything quite slowly and high CPU load, as for you. Of course we
> didn't generate thumbnails for the uncommon screen resolutions, as we could
> save storage space that way. In your case I think you only have one thumbnail
> for each image, which will not be that much storage space needing.
>

No, I have currently at lest three.


>
>>>> Its probably worth using php to do work the database does badly, but I'd
>>>> never use it to do what the database does well..
>>> When doing the same query many times, it's worth to think about using
>>> memcached to store the result, just see to remove the cached data when
>>> you
>>> make an update.
>>>
>> SQL does that anyway, all by itself.Makes the second identical 10 second
>> query happen in a flash!
>>
>> Until you change a record...
>>
>
> MySql has some caching features, put those are far short time, if you have a
> busy site, mysql may not have a heavy query cashed anymore a hour later, while
> using memcached it wouldn't be a problem.
>

Then allocate more memory to mysql.
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

The Natural Philosopher wrote:
> J.O. Aho wrote:
>> The Natural Philosopher wrote:
>>> J.O. Aho wrote:
>>>> The Natural Philosopher wrote:
>>
>>>>> Its probably worth using php to do work the database does badly,
>>>>> but I'd
>>>>> never use it to do what the database does well..
>>>> When doing the same query many times, it's worth to think about using
>>>> memcached to store the result, just see to remove the cached data when
>>>> you
>>>> make an update.
>>>>
>>> SQL does that anyway, all by itself.Makes the second identical 10 second
>>> query happen in a flash!
>>>
>>> Until you change a record...
>>>
>>
>> MySql has some caching features, put those are far short time, if you
>> have a
>> busy site, mysql may not have a heavy query cashed anymore a hour
>> later, while
>> using memcached it wouldn't be a problem.
>>
>
> Then allocate more memory to mysql.

You still have the problem that you won't know if your query is stored in the
query cache or not.

--

//Aho
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

J.O. Aho wrote:
> The Natural Philosopher wrote:
>> J.O. Aho wrote:
>>> The Natural Philosopher wrote:
>>>> J.O. Aho wrote:
>>>>> The Natural Philosopher wrote:
>>>>>> Its probably worth using php to do work the database does badly,
>>>>>> but I'd
>>>>>> never use it to do what the database does well..
>>>>> When doing the same query many times, it's worth to think about using
>>>>> memcached to store the result, just see to remove the cached data when
>>>>> you
>>>>> make an update.
>>>>>
>>>> SQL does that anyway, all by itself.Makes the second identical 10 second
>>>> query happen in a flash!
>>>>
>>>> Until you change a record...
>>>>
>>> MySql has some caching features, put those are far short time, if you
>>> have a
>>> busy site, mysql may not have a heavy query cashed anymore a hour
>>> later, while
>>> using memcached it wouldn't be a problem.
>>>
>> Then allocate more memory to mysql.
>
> You still have the problem that you won't know if your query is stored in the
> query cache or not.
>
I dont care, as long as it is fast.
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

The Natural Philosopher wrote:
> J.O. Aho wrote:
>> The Natural Philosopher wrote:
>>> J.O. Aho wrote:
>>>> The Natural Philosopher wrote:
>>>>> J.O. Aho wrote:
>>>>>> The Natural Philosopher wrote:
>>>>>>> Its probably worth using php to do work the database does badly,
>>>>>>> but I'd
>>>>>>> never use it to do what the database does well..
>>>>>> When doing the same query many times, it's worth to think about using
>>>>>> memcached to store the result, just see to remove the cached data
>>>>>> when
>>>>>> you
>>>>>> make an update.
>>>>>>
>>>>> SQL does that anyway, all by itself.Makes the second identical 10
>>>>> second
>>>>> query happen in a flash!
>>>>>
>>>>> Until you change a record...
>>>>>
>>>> MySql has some caching features, put those are far short time, if you
>>>> have a
>>>> busy site, mysql may not have a heavy query cashed anymore a hour
>>>> later, while
>>>> using memcached it wouldn't be a problem.
>>>>
>>> Then allocate more memory to mysql.
>>
>> You still have the problem that you won't know if your query is stored
>> in the
>> query cache or not.
>>
> I dont care, as long as it is fast.

It won't be fast if the wrong data is cached, you will need to actually do the
query over and over again.

--

//Aho
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

J.O. Aho wrote:
> The Natural Philosopher wrote:
>> J.O. Aho wrote:
>>> The Natural Philosopher wrote:
>>>> J.O. Aho wrote:
>>>>> The Natural Philosopher wrote:
>>>>>> J.O. Aho wrote:
>>>>>>> The Natural Philosopher wrote:
>>>>>>>> Its probably worth using php to do work the database does badly,
>>>>>>>> but I'd
>>>>>>>> never use it to do what the database does well..
>>>>>>> When doing the same query many times, it's worth to think about using
>>>>>>> memcached to store the result, just see to remove the cached data
>>>>>>> when
>>>>>>> you
>>>>>>> make an update.
>>>>>>>
>>>>>> SQL does that anyway, all by itself.Makes the second identical 10
>>>>>> second
>>>>>> query happen in a flash!
>>>>>>
>>>>>> Until you change a record...
>>>>>>
>>>>> MySql has some caching features, put those are far short time, if you
>>>>> have a
>>>>> busy site, mysql may not have a heavy query cashed anymore a hour
>>>>> later, while
>>>>> using memcached it wouldn't be a problem.
>>>>>
>>>> Then allocate more memory to mysql.
>>> You still have the problem that you won't know if your query is stored
>>> in the
>>> query cache or not.
>>>
>> I dont care, as long as it is fast.
>
> It won't be fast if the wrong data is cached, you will need to actually do the
> query over and over again.
>
I suspect mysql's ability to optimise is slightly better than anything I
could come up with in php in under several weeks.
 >> Stay informed about: Which is faster - MySQL query, or PHP loop 

The Natural Philosopher wrote:
> J.O. Aho wrote:
>> The Natural Philosopher wrote:
>>> J.O. Aho wrote:
>>>> The Natural Philosopher wrote:
>>>>> J.O. Aho wrote:
>>>>>> The Natural Philosopher wrote:
>>>>>>> J.O. Aho wrote:
>>>>>>>> The Natural Philosopher wrote:
>>>>>>>>> Its probably worth using php to do work the database does badly,
>>>>>>>>> but I'd
>>>>>>>>> never use it to do what the database does well..
>>>>>>>> When doing the same query many times, it's worth to think about
>>>>>>>> using
>>>>>>>> memcached to store the result, just see to remove the cached data
>>>>>>>> when
>>>>>>>> you
>>>>>>>> make an update.
>>>>>>>>
>>>>>>> SQL does that anyway, all by itself.Makes the second identical 10
>>>>>>> second
>>>>>>> query happen in a flash!
>>>>>>>
>>>>>>> Until you change a record...
>>>>>>>
>>>>>> MySql has some caching features, put those are far short time, if you
>>>>>> have a
>>>>>> busy site, mysql may not have a heavy query cashed anymore a hour
>>>>>> later, while
>>>>>> using memcached it wouldn't be a problem.
>>>>>>
>>>>> Then allocate more memory to mysql.
>>>> You still have the problem that you won't know if your query is stored
>>>> in the
>>>> query cache or not.
>>>>
>>> I dont care, as long as it is fast.
>>
>> It won't be fast if the wrong data is cached, you will need to
>> actually do the
>> query over and over again.
>>
> I suspect mysql's ability to optimise is slightly better than anything I
> could come up with in php in under several weeks.

It's not good enough, thats why they together with others came with memcached
which you have support for in PHP.


--

//Aho
 >> Stay informed about: Which is faster - MySQL query, or PHP loop