Programmer overkill (MySQL)

I have a peeve about MySQL.

Oh, not about MySQL directly: it's great. I love it, it's wonderful, no complaints. It's the people who use it when they don't need to that get me shaking my head and talking to myself.

This falls in the same general category as my previous rant about text vs. binary. Some people use MySQL for idiotic purposes.

If you have a couple of hundred records to store, a database of any kind is just plain ridiculous. You can open that file and find anything by brute force far quicker than you can make a database connection and process a SELECT. Furthermore, the data is immediately available to any other program, to shell scripts, and to human beings with text editors.

To show that, I created a small MySQL database with 99 records and a text file to match. A Perl program reads through the text file and prints it out. The other Perl program does nothing but connect to the database, disconnect and exit. Here's the code:

# text version
#!/usr/bin/perl
open(I,"tst");
while(<I>) {
 chomp;
 print "$_\n";
}

# mysql version
#!/usr/bin/perl5
use DBI;
$dbh=DBI->connect('DBI:mysql:foo:localhost','root','8k3t2s95', {PrintError=>1, RaiseError=>1}) or die "$DBI::errstr";
$dbh->disconnect;
exit 0;
 

The MySQL doesn't read a single record and prints nothing to the screen. Watch:

$ time mysqlversion.pl
real	0m0.187s
user	0m0.108s
sys	0m0.070s
$ time textversion.pl
foo, 1
foo, 2
foo, 3
...
foo, 97
foo, 98
foo, 99

real    0m0.020s
user    0m0.011s
sys     0m0.001s
 

The text version, which actually reads and prints all the records, takes about a tenth of just the startup time for the MySQL version.

So you say, sure, but that's just a hundred records. Let's try a thousand, shall we?

foo, 1
foo, 2
foo, 3
...
foo, 997
foo, 998
foo, 999

real    0m0.069s
user    0m0.005s
sys     0m0.016s
 

See what I mean? That's reading and printing one thousand records, and it still does it before MySQL is ready to do anything at all.

Again, it's not MySQL that's the problem. If you choose to drive an eighteen wheeler to pick up groceries, we can't blame the truck. Actually, if it were just you making that choice, I wouldn't care, but when some otherwise nice piece of software insists upon using MySQL for no good reason, I get annoyed. And I mutter. And I rant here, of course.

Got something to add? Send me email.

3 comments

Wed Sep 14 16:05:04 2005: 1087   bruceg2004


I have peeves about people not using the right tool for the job, too. I often encounter huge spreadsheets, where a database should be used. I cannot tell you how many 50+ MB Excel spreadsheets I come across, that are being used like a database. Your example is a good one. How many people out there encounter silly uses of technology that are under/over kill?

- Bruce



Thu Nov 17 21:50:20 2005: 1365   dragev




Though your test clearly demonstrates how expensive database connections are, I wondered why you didn't try to make your point using a database-like example. You said your test was based on "...a small MySQL database with 99 records and a text file to match". My assumption is that your test database only had a single table --- now that's quite unrealistic and a good example of how to use "MySQL for idiotic purposes".


But what about using MySQL for a few related tables, even if the number of records is relatively small? Are text files, still the way to go?




Thu Nov 17 22:00:29 2005: 1366   TonyLawrence


But people do stupid things like that..

Anyway, I often handle multi values with perl split:

$line="$var0|$var1|var2"
@stuff=split /\|/,$line

Still way faster than MySql for small data sets.

------------------------

Have you tried Searching this site?

This is a Unix/Linux resource website. It contains technical articles about Unix, Linux and general computing related subjects, opinion, news, help files, how-to's, tutorials and more.

Contact us