Basically I need a database that is good with queries like
I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB and found out that the query like "/abc/" works really badly, and Mongo indexes support only "/^abc/".
My database structure is very simple.
Example query in PostgreSQL:
SELECT DISTINCT(id), title FROM data AS data INNER JOIN datatosynonym AS dts ON dts.data_id = data.id WHERE dts.synonym_simple LIKE "%abc%"
And in MongoDB
Where synonymssimple is an array of strings.
Example data in PostgreSQL
id | title | timestamp 1 | Abc | 1145836800 2 | Qwe | 1145836800
id | synonym_simple | data_id 1 | abc | 1 2 | bac | 1
My benchmarks show the following results:
- PostgreSQL with b-tree indexes and %abc% query - ~15ms per query
- PostgreSQL with b-tree indexes and abc% query - ~1ms per query
- PostgreSQL with GIN indexes and %abc% query - ~1.5ms per query
- PostgreSQL with GIN indexes and abc% query - ~1ms per query
- MongoDB without indexes and /abc/ query - ~25ms per query
- MongoDB with b-tree indexes and /abc/ query - ~80ms per query
- MongoDB with b-tree indexes and /^abc/ query - ~0.25ms per query
Sadly I can't use /^abc/ query.