OT: MySQL and DBI efficiency question

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

Is it the amount of records you fetch that determines how much cpu memory you use or is it the amount of rows the database table actually has?

For example, whats puts more strain onto a web server:

1.) fetch 500 rows from a database table with 5,000,000 sets.
2.) fetch 20,000 rows from a database table with 1,000,000 sets.
[download]

Thanks,
Henry

Comment on OT: MySQL and DBI efficiency question Download Code

Replies are listed 'Best First'.
Re: OT: MySQL and DBI efficiency question by duff (Parson) on Aug 02, 2006 at 05:19 UTC
They both put the same strain on a web server because the RDBMS server is on another box. Seriously though, it depends on all sorts of things. You may want to benchmark a representative case if you have some actual data. duff	[reply]
Re: OT: MySQL and DBI efficiency question by marto (Cardinal) on Aug 02, 2006 at 08:26 UTC
In addition to the great advice you have already been given you may want to look at The MySQL Benchmark Suite and BENCHMARK, both, oddly enough, from the MySQL documentation. Why not construct your own benchmarking tests catered for your environment, which you have not supplied a great deal of information about? From a Perl perspective there is Benchmarking Your Code from the Tutorials section of this site. Hope this helps Martin	[reply]
Re: OT: MySQL and DBI efficiency question by perrin (Chancellor) on Aug 02, 2006 at 14:30 UTC
If you're asking which one takes more memory, the answer is #2. DBD::mysql will fetch all of those rows into memory at once, even if your code is just iterating through them. The only way to avoid this is with the "mysql_use_result" option, which is documented in the DBD::mysql manual.	[reply]
Re: OT: MySQL and DBI efficiency question by jesuashok (Curate) on Aug 02, 2006 at 05:24 UTC
Hi It depends upon the following factors :- web server caching CPU configuration Query you write to get data from DB amount of data you retreive from DB Database design "Keep pouring your ideas"	[reply]