donderdag 20 februari 2014

Why I like PostgreSQL more than MySQL: functional indexes

Indexes enable a database to lookup records quickly. They contain a sorted subset of columns from the target table, which makes them small and easy to traverse.

However, they can only help if the clause that your query uses can be solved by looking at the data that is stored in the index. For example, a query containing the clause "WHERE YEAR(datefield)=2014" will not use an index.

In MySQL the only thing you can do is write a trigger that saves the output of YEAR(datefield) into a separate column or even a separate table, and index that. This is very clumsy and difficult to use in your application.

In PostgreSQL you can simply create an index on YEAR(datefield) and the database will use that if a clauses mentions YEAR(datefield), and it will of course automatically update the index just like any other index.

CREATE INDEX datefield_year_idx ON foo (EXTRACT(YEAR FROM datefield));

This type of indexing can also be used on XML and JSON fields.