Hacker News Reader

Comment by St-Clock | Hacker News Reader

I am confused. How hard is it to create an index in MySQL? I don't have much experience with MySQL, but with PostgreSQL, it is very easy.

In addition, there are some "advanced" features that may not be provided by MongoDB: you can choose your index type, rebuild the index, update the stats that will help the query optimizer decide if the index should be used at all, etc.

View on HN

It's not hard, but it's something no-sql databases aren't terribly good at, making it a differentiator for MongoDB. In SQL everything ends up being an exercise in joins, in No-SQL everything ends up being an exercise in map-reduce. The ability to create indexes, like you can easily do in SQL databases, is a handy feature when you have relational or quasi-relational data.

It's not that the actual command you enter to create an index is hard. It's just that when you have a table with millions or billions of rows, you have to figure out how to create the index without stopping the world for hours.

With postgresql, create index does not block read, only write. You can also use the CONCURRENTLY parameter and writes/updates won't be blocked, but the index creation takes longer (the db has to wait until no transaction potentially involving the index has stopped).

Not sure what other strategies exist to reliably build an index.

http://www.postgresql.org/docs/current/static/sql-createinde...

Reply on news.ycombinator.com