As part of the Netezza replacement project I’ve been working on, I ran a quick evaluation of MySQL 5.x. before testing PostgreSQL. I didn’t have time to conduct a thorough evaluation, but I quickly found out that MySQL does not handle queries ran against views very well. I ran some tests by creating simple views against some very large tables (40+ million rows). Running a query against the base table was many times faster than running the same query against a view which did not even contain a WHERE clause. I’ll need to do some additional research, but it seems that the implementation of views in MySQL lags far behind the PostgreSQL implementation. It’s obviously not very surprising, since support for views was added to MySQL only in version 5.0.
Archive for the ‘PostgreSQL’ Category
MySQL vs. PostgreSQL for Data Warehouses that use Views
Tuesday, November 7th, 2006PostgreSQL Performance Checklist
Tuesday, November 7th, 2006I found some very valuable tips in the web site of an unpublished book: Power PostgreSQL by Josh Berkus and Joe Conway. If you are working with large data sets and complex queries, pay special attention to the section that discusses work_mem.
Tuning PostgreSQL for Data Warehousing
Thursday, October 26th, 2006I’m currently working in a project that will replace a Netezza database with PostgreSQL 8.1. Early test results have been very encouraging, even though I haven’t found much information online about how to best tune PostgreSQL when dealing with complex queries running against very large tables. I will share what I learned in future posts.