All the Perl that's Practical to Extract and Report

SQLite and MySQL

ziggy on 2003-03-24T15:19:09

I'm using SQLite on a couple of projects at the moment. I'm writing a Cocoa app in ObjC, and I want the thing to just work, so I need an embedded database. I'm not especially happy that every column is a TEXT block, but it does make some things a lot easier.

I showed Matt Sergeant's benchmarking results to David Axmark, one of the core MySQL developers. Overall, David was pretty skeptical that SQLite could be faster than MySQL. His observations:

  • A lot of the overhead between SQLite and MySQL is due to the TCP/IP stack. Embedded MySQL should have comparable performance characteristics to SQLite
  • The use of strings for all columns comes from a fascination with Tcl; it's not a very interesting solution in terms of data integrity, but it is certainly understandable given the approach the designers took
  • Database structure operations (CREATE INDEX, DROP TABLE) are slower in SQLite because they took the entire-database-in-one-file approach; MySQL is faster here because indexes and tables are separate files.
  • It's fair that SQLite hasn't optimized operations that change a database's structure (CREATE INDEX, DROP TABLE, etc.), since their assertion is these operations occur infrequently. David was quick to point out however that a few MySQL customers create a lot of temporary tables (hundreds per minute, IIRC). These are certainly edge cases though.

Makin' tables!

jjohn on 2003-03-25T12:24:00

Actually, I know of at least on medium-sized company that creates tables on the fly for their web application to hold session data. That's right, every session gets a new table. They use MySQL. I like MySQL and it's attendent admin and client utilities more the Postgres. Do I miss stored procedures? Sure, but I think those are overrated. At gnat pointed out, SQL has exploded in popularity and there will be "language"-side (or middleware) tools that help with cascading deletes and other niceties that do easy the pain of DB applications. I just don't see what the big who-ha is about Postgres, but then I'm not doing financial transactions. MySQL is very New Jersey-style, but so is Linux.