Which database engine to choose?

Question

MySQL

There is a project. Currently using MySql.
A number of InnoDB tables with data on the order of 1 gigabyte each.
Queries like selec sum(money) from table where field1=5 and field2=11 and field3='asdasdd' are made
and fulltext searches are often used.
There are tens of thousands of such requests. The set of fields is set by the user. It doesn't work very fast.
Indexes are used (created "on the fly" for the appropriate queries).
I'm considering changing the database. Please tell me the options.

Answer 1

Elasticsearch as search index and postgresql as storage. At least that's my choice.

Answer 2

Full text search in mysql is just a crutch. Operator like for 5-page text...
Normal full-text engines break the text into indexable lexemes according to a given dictionary, removing duplicates and putting down positions, which allows calculating rankings (essentially relevance). Postgresql can do all this, although it is not a specialized search engine.

Answer 3

I agree with the @sim3x post , and I will add a little from myself.
When I needed to solve a problem with full-text search on data from MySQL, Sphinx helped me a lot. It is noticeably faster than the standard mechanism, and the functionality is much wider. A couple of days is enough to study documents, articles. You can combine Sphinx search, and search by source data (for example, by numeric fields).

Answer 4

I advise you to take a close look at postgresql. Everything you need is there :) Have your own fts, many different specialized types, including json, array, etc.