Posted on June 20, 2009 at 11:57 pm

Wall of shame?

I am torn between showing the whole thing as a wall of shame item or just ranting about an anonymous (open source) user management product. It’s not alone in this sin, I’ve seen the same problem in *expensive* database driven shopping cart and user management apps.

What problem is this that I am speaking of? LACK OF INDEXES. Seriously. If you have a sessions table, and you’re searching for old sessions to expire YOU NEED AN INDEX ON THE TIME COLUMN.

Another fun one is seeing ‘delete’ functions that don’t take care of the extra data referencing the main table, this is largely MySQL’s fault for not having foreign key support until lately. It really is the database’ job to deal with that.

1.8 million sessions occupying about 500MB or so, bringing a server down to it’s knees. CREATE INDEX sessions_last_active_idx ON sessions (last_active); and suddenly everything is 100% kosher.

What I am saying is, either find someone who has a damned clue to design your DB Schema INCLUDING INDEXES with you, and review your EVERY SELECT, DELETE AND UPDATE, or learn how to do it yourself, or don’t write the app.