APLawrence.com -  Resources for Unix and Linux Systems, Bloggers and the self-employed

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
while(<I>) {
 print "$_\n";

# mysql version
use DBI;
$dbh=DBI->connect('DBI:mysql:foo:localhost','root','8k3t2s95', {PrintError=>1, RaiseError=>1}) or die "$DBI::errstr";
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.

(OLDER)    <- More Stuff -> (NEWER)    (NEWEST)   

Printer Friendly Version

-> -> Programmer overkill (MySQL)


More Articles by

Find me on Google+

Click here to add your comments
- no registration needed!

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:

@stuff=split /\|/,$line

Still way faster than MySql for small data sets.

Don't miss responses! Subscribe to Comments by RSS or by Email

Click here to add your comments

If you want a picture to show with your comment, go get a Gravatar
Kerio Samepage

Have you tried Searching this site?

Unix/Linux/Mac OS X support by phone, email or on-site: Support Rates

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

Jump to Comments

Many of the products and books I review are things I purchased for my own use. Some were given to me specifically for the purpose of reviewing them. I resell or can earn commissions from the sale of some of these items. Links within these pages may be affiliate links that pay me for referring you to them. That's mostly insignificant amounts of money; whenever it is not I have made my relationship plain. I also may own stock in companies mentioned here. If you have any question, please do feel free to contact me.

I am a Kerio reseller. Articles here related to Kerio products reflect my honest opinion, but I do have an obvious interest in selling those products also.

Specific links that take you to pages that allow you to purchase the item I reviewed are very likely to pay me a commission. Many of the books I review were given to me by the publishers specifically for the purpose of writing a review. These gifts and referral fees do not affect my opinions; I often give bad reviews anyway.

We use Google third-party advertising companies to serve ads when you visit our website. These companies may use information (not including your name, address, email address, or telephone number) about your visits to this and other websites in order to provide advertisements about goods and services of interest to you. If you would like more information about this practice and to know your choices about not having this information used by these companies, click here.

This post tagged: