How to properly organize the storage of category links with a post?

Question

PHP

Good afternoon. I got one project on DLE in which there are many categories and posts.
I looked at a selection of category posts, it looks like:

SELECT * FROM dle_post WHERE category regexp '(17)'

which is very heavy on a site with high traffic.
How I decided to solve the issue:
- Add 1 table with the link of the post and category categories_to_post with the fields: id, post_id, category_id.
And replace the selection with

JOIN categories_to_post ON dle_post.post_id = categories_to_post.post_id  WHERE categories_to_post.categoiry_id = 'ид категории'

Interested in how this is the correct solution to the issue, whether it will reduce the load and other possible solutions.

Answer 1

also how to reduce
only the inner join (if you need posts from this category)
+ if there are a lot of records, then you will need to create indexes for
dle_post.post_id
and for the link table
categories_to_post.post_id
categories_to_post.categoiry_id