Thursday, October 27, 2011

MySQL Bizarro World


Getting used to MySQL has been a real challenge for me. Most everything I know about databases is backward in this MySQL world.

In the REAL WORLD,
table names are case-insensitive.

In MySQL WORLD,
table names are case-sensitive. Maybe. Depending on what platform you're running on.

in the REAL WORLD,
queries against VARCHAR data are case-sensitive.

in the MySQL WORLD,
queries against VARCHAR data are case-insensitive. (So is every other use of the data. Which can cause immeasurable pain w/r/t UNIQUE constraints if you weren't prepared for it.)

in the REAL WORLD,
database connections are expensive, and complex queries are optimized well. If you can connect to the database once and issue a single complex query, you'll get much better performance than if you make repeated connections and issue large numbers of simple queries.

in the MySQL WORLD,
database connections are cheap, and complex queries are optimized badly. If you make repeated connections and issue large numbers of simple queries, you'll get much better performance than if you connect to the database once and issue a single complex query.

I think the conclusion is obvious: MySQL was written by programmers from Superman's Bizarro World.

Incidentally, the Dayton Oracle User Group is planning a MySQL-themed meeting in the mid-term future. If you'd like to get involved - as an attendee or a speaker - let me know!

Hello!

4 comments:

Craig Maloney said...

In the real world, you can have transactions, speed, row locking, and full text search.

In MySQL world, you get two out of four. And if you chose something differently, you get to migrate to a new engine.

MySQL: The database that makes SQLite look like a viable option.

Hartmut said...

in the MySQL world case sensitivity of VARCHAR depends on the charset/collation associated with that VARCHAR ...

hint: there are both case sensitive and case insensitive collations

Hartmut said...

@Craig:

3 out of four, number 4 (fulltext for InnoDB) being worked on as we speak

and for SQLite: yes, its a perfect alternative as long as you don't plan to run more than one query in parallel, so fine for contact data on phones for example, or for the config data in MySQL Workbench, but not for anything serverish ...

Craig Maloney said...

@Hartmut: For simple tasks, there's SQLite. For everything else, there's MasterCa.... er, I mean PostgreSQL.