.... continued from part one

This is the second in a series of four excerpts from the MySQL presentation I gave to my local Linux Group a while back. The presentation was inspired by Arjen Lentz's outline but I added a few of my own pet peeves. And I have to admit that the title is a bit deceptive. I am going to expose more than seven sins when all is said and done, but I just like the alliteration and the theological reference. I beg your indulgence for the technical inaccuracy.

Guilty, Guilty, Guilty, and even more guilty because I knew it was a sin and I did it anyway. "Only this once" has this tendency to become legacy code that grinds away inefficiently for years. When you order a table by RAND() you have to touch every row in the table to put an order on it. Then you throw all but one of them away? Look at that innocent-looking, but cycle-sucking query that can be found in almost any code you care to name:

SELECT ..... ORDER BY RAND() LIMIT 1;

The reason why everyone does this dumb thing above is that it's surprisingly hard to get a random row correctly and randomly. With the two argument form of LIMIT, the following works:

SELECT CAST(1+COUNT(*) * RAND() AS UNSIGNED) FROM country;

Put the result into an application variable. Let's say $foo

SELECT * FROM country LIMIT $foo,1

CREATE TABLE tbl (replace INT, count INT);

ERROR 1064: You have an error in your SQL syntax 

near 'replace INT, count INT)' at line 1 

Ummm.... MySQL has a lot of reserved words. If you must use them as fieldnames, you can enclose everything in backticks. I don't recommend it. For one thing the backticks are annoying, ugly, and easy to forget. For another thing it obfuscates things to use reserve words in a different namespace. Here is what it lookes like all backtickified.

 CREATE TABLE `select` (`replace` INT, `update` INT);   

I prefer something like

 CREATE TABLE selector ( replacement INT, refresher INT); 

Another gotcha with this sin is importing a table from an older version of mySQL. Things that weren't reserve words before suddenly are. My old version (4.0) had a table called "reads". MySQL 5 did not like it, and what fixed it was making up a new name for the table. I guess "reads" must somehow be a reserve word now. Thanks to encapsulation, the only place the reads table was called in the application was in one class. So I opted to change its name, and made the corresponding changes in the application, rather than backtick it and risk all the errors associated with that for the rest of my life. It would be a bigger problem if there were many applications accessing this data and the person maintaining the database did not know about them.

$query = "SELECT * FROM ...";

... 

$id = $row[0];

$name = $row[1];

This is soooo 1980. Early SQL's referred to data by their position, but being able to use field names as hashes has been available for quite some time. Not only is it bad from a human engineering point of view but if your friendly neighborhood DBA decides to reorder the fields for efficiency....oops your application is seriously broken and you will have a devil of a time fixing it. Use symbolic names in your application code!

Well that's all the sins for today. Those weren't so bad were they? We like to keep them down to bite sized chunks that you can swallow easily. Almost everyone (including me) is guilty of "Galloping Inefficiency" at some time when they are in a hurry and don't want to bother to look up the CAST expression in their notes. I hope you are not in the habit of using reserved words as table or field names, and for heavens' sake use associative arrays when referring to database results! These are still pretty basic sins and they will bite you in pretty obvious ways if you do them, except for the inefficiency sin will quietly eat away your performance benchmarks for years. If your application is working, but slowly, look for RANDOM LIMIT 1 queries that are heavily used. If you are guilty, I'm a forgiving person so I'll forgive you, but don't do it any more, OK? I have a piping hot fresh batch of more diabolical sins at part III

About the author...

Colleen Dick has been a database techie, php developer, security analyst, and social networker for a long time. She has single-handedly developed a dynamic joke site, a IT certification practice exam repository, social network for interactive video gamers, a community calendar, and an online ticket sales ecommerce site. When she comes off her extended break from programming she will be looking into ning and dojo. Ta Da! No promo links. Thank you for reading.