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.
cheers
tachyon
In reply to Re^3: fetch row or fetchall
by tachyon
in thread fetch row or fetchall
by hakkr
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |