How best to organize such a system?

Faced with the fact that the main section of the service recently (due to the growth of the audience) loads the processor on 100%. There may be other options for the organization of this section or any other technologies that might be better.

Task:
To show the user ideas (posts) that he had not yet seen and tags which fit his filter. The filter is a list of tags, some of which may contain characters that change their behavior. For example, the tags whose ideas should not be, the tags that should be in the same idea at the same time, the tags must be in every idea. Tags containing the symbol *, cover all matching the mask options immediately.
Ideas should be sorted by the date of the last restart (the transfer field is active from 0 to 1 raises the idea up).

Now made so:
Mysql. Table ideas, ideas_tags, ideas_index and ideas_seen.
The query looks like this:
select * from ideas where
id in (select idea_id from ideas_tags where tag_id in (123,123,13342,232,133,82))
and id not in (select idea_id ...)
and ideas.active=1
and id not in (select idea_id from ideas_seen where user_id=182736)
order by (select id from ideas_index where idea_id=ideas.id) desc
limit 10
Sometimes filters are added along the length of the ideas.

Are there options for how the same functionality easier to implement for the server method?
June 10th 19 at 16:15
3 answers
June 10th 19 at 16:17
Not necessarily the logic of the query affects its performance. To a greater extent, it can affect the specificity of the data, physics data placement, etc.

If you are sure that the problem lies in this query, we need not analyze the request and its execution plan (explain ...). Without a plan nothing sensible to say. We can only offer an abstract idea for optimization:

1. - designed indexes
2. denormalization
3. materialized views
4. States for dividing
5. types of engines
June 10th 19 at 16:19
order by (select id from ideas_index where idea_id=ideas.id) desc

At least it is necessary to remove ideas_index to the query and to the table ideas to add a new field sort (denormalize).
June 10th 19 at 16:21
In addition to the suggestions above. I would be optimised where
Conditions are simple and effective from the point of view of the indices to move up further in the increase of inefficiency.
After all, with each new condition the sample volume decreases, so the server will have to do less passes
then in id and id not in I would be reduced to two, it will be faster than several times to dig a sample (especially if it is large or do not fit in memory) for comparison with the list. Ie first prepare a list, then once you compare.
Of course the server itself should be optimized for such requests to have enough allocated memory.

Find more questions by tags Server optimizationDatabasesMySQL