Like a lot of you, I’ve been following with interest Percona’s testing of the open source column databases. One thing I think is pretty cool about some column databases that work with MySQL is that they don’t require you to create indexes. The reason is, in general, the column is the index. Not having to create indexes is nice because lots of indexes can really bog down a database if you’ve got a lot of load or DML activities because the indexes have to be maintained for all data input and alterations.
In Percona’s test, they showed the load time for all the different databases, but I noticed that the times didn’t include the index creation for LucidDB or MonetDB. I decided to follow Vadim’s link on the LucidDB index creation and totaled up the time it took to create the indexes. For the index and statistics times, it was 384,314 seconds and when you add that to the 140,736 seconds for the table load, you get 6 days just to create the database. That’s quite a difference from the 6 hours for InfiniDB and 14 hours for InfoBright, both of which don’t need or use indexes.
I’m sure indexes supply a benefit for some column DB’s in various use cases, but if the database was real dynamic and required a lot of new objects with indexes be added, continuous heavy loads, or DML, it would seem that indexes could really put a ding in things. In that case, it would seem column DB’s thatdon’t require indexes could have an edge there.