Jackson Miller

I have had two major disappointments with MySQL this week. I am starting to seriously rethink my options.

Views in MySQL
Views in MySQL are nothing more than a persistent query. There is no optimization performed. There is no data cacheing. There is no such thing as a Materialized View. For a software developer, a view in MySQL is nothing more than storing a query as a variable that can be reused throughout the application. If you ask me, these are Views in name only. I can’t think of a meaningful use for MySQL Views.

Partitioning in MySQL
Database partitioning is a wonderful thing. I have used home-grown partitioning schemes in MySQL and PostgreSQL based on dynamic table names in the application code. I have also used partitioning in Oracle. What I think is the most common use of database partitioning is to separate data based on customer accounts. This pattern prevents the size of one customer’s database from affecting the query speed of other customers. The problem is that MySQL can’t do this. In MySQL, database partitions require you to either create all tables without primary keys or unique keys or to partition based on every column in the every primary and unique key. This essentially prevents customer data segregation with partitioning.

statzen is essentially a massive data warehouse and reporting system. The amount of data will rival many of the big corporations I have worked with. Views and Partitioning vital. My options are to either implement work-arounds in MySQL (which I think I can do for both), or to use another database engine. Oracle could handle this like nobody’s business. PostgreSQL also supports partitioning via table inheritance which I have used with a large database before. SQL Server is out of the question, and DB2 is a miserable piece of garbage that I would never choose to use.

I know MySQL can handle it, but that doesn’t mean it should. I am not a big fan of a database that has every single application constraint, trigger, procedure, etc all defined in the database. I just want to be able to segregate data and optimize it for reporting. MySQL is supposed to be the fast database, but it would be lots faster if it really had Views and Partitioning.