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

Programmer overkill (MySQL)

© September 2005 Tony Lawrence

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.

Got something to add? Send me email.

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

Printer Friendly Version

-> Programmer overkill (MySQL)


Inexpensive and informative Apple related e-books:

Take Control of iCloud

Take Control of OS X Server

Take Control of the Mac Command Line with Terminal, Second Edition

El Capitan: A Take Control Crash Course

iOS 8: A Take Control Crash Course

More Articles by © Tony Lawrence

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.


Printer Friendly Version

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

Printer Friendly Version

Several people have told me that my inability to suffer fools gladly is one of my main weaknesses. ((Edsger W. Dijkstra)

Linux posts

Troubleshooting posts

This post tagged:



Unix/Linux Consultants

Skills Tests

Unix/Linux Book Reviews

My Unix/Linux Troubleshooting Book

This site runs on Linode