How to work with filtered data?

Project in php mysql
Prompt, please, than you can search the site to optimize?
I don't need full text search, and (for example) sphinx and elasticsearch it is only necessary for this?

In General, the task of filtering number of rows, tens of thousands (the search itself and not)...

Thank you!
(can upload in app using redis and then only to filter... or through mongo)..

Give advice or maybe a link to (reflections, tests...) for this reason.
July 9th 19 at 10:42
2 answers
July 9th 19 at 10:44
If you do not need data aggregation from different tables - you will need create indexes on the tables. All filtering you can do with indexes.
Data aggregation? I mean join? Needed Yes. What then advise? - al commented on July 9th 19 at 10:47
Ideally, review the dB schema and get rid of joynow. Indexes in mysql will work much faster and will give less load than sphinx and elasticsearch - lennie.Beatty commented on July 9th 19 at 10:50
: and how is that rate?
Make a separate table Assembly, the maximum denormalized and work on frontend with it, through it and filter. And on the backend when you insert these rows to insert at once in both. - al commented on July 9th 19 at 10:53
If the growth of the database say it is normal. But I would instead of inserting from both a table - create a trigger in the dB to populate the "Assembly" table. Then the entry in dB will remain atomic. - lennie.Beatty commented on July 9th 19 at 10:56
: Thank you for the tip! - al commented on July 9th 19 at 10:59
The index of the text (in MySQL) has a limit on the maximum length, and full-text search (which can also be used for filter) hellishly slow. - Alyson.McClure commented on July 9th 19 at 11:02
July 9th 19 at 10:46
Well duck and filter the same mysql.
The index was devised for this purpose :)
Yes, I understand that the indices for and invented. But is this the most effective solution? Maybe there is something I miss (not understand). - al commented on July 9th 19 at 10:49
The sample index is the fastest. What kind of optimality talking about? Indicate the problem (slow fetch or what?) - lennie.Beatty commented on July 9th 19 at 10:52

Find more questions by tags MySQLRedisElasticsearchSphinx