Re^3: fetch row or fetchall

[tachyon]

count(*) is an optimised query on MySQL (and probably most other DBs)

mysql> describe select count(*) from global_urls_http;
+------------------------------+
| Comment                      |
+------------------------------+
| Select tables optimized away |
+------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from global_urls_http;
+----------+
| count(*) |
+----------+
|  9908618 |
+----------+
1 row in set (0.00 sec)

I will guarantee you that pulling 10 million odd rows just to get the count above will take longer than 0.00 sec :-)

Pulling back 10,000 rows just to get the count and save an extra query has some potentially very undesirable side effects.

Assuming 512Byte records the base data is 5 MB - even with a disk transfer speed of 50 MB/sec this is a minimum 1/10th second (probably more like 1/2 a second in the real world) just to pull that data off the disk. Given most DBs ability to execute hundreds of queries per second two queries is likely to be sinificantly faster as the expense of pulling 100x as much data as you really want is quit real.

Anyway by the time you get that data into a perl array it is probably 10 MB or more. Now this may not seem like a problem until you get your head around the fact that Perl essentially never releases memory back to the OS. It does free memory but typically keeps that memory for its own reuse. So why does that matter? Well if you have 10-20 long running parallel processes (mod_perl for example) the net result is an apparent memory leak over time. As each child makes a 'mega' query it it grabs enough memory for the results. The net result is that each child grows to the size of the largest query it has ever made.

[hakkr]

very interesting tachyon, so as always it really depends on the details of the data volumes to be fetched.

In general though I take from this that it is a good idea just to fetch what you need to avoid memory/performance problems. thanks again