How to correctly link posts and categories (product and categories, if you like)?

Question

MySQL

The variant with a linking table ala:
Post
post_id (PK)
title
Category
category_id (PK)
title
Post_Category
post_id (PK)
category_id (PK)
is certainly good, but with tens of thousands of posts it is slow.
DB: muscle, InnoDB

Answer 1

Nonsense, tens of thousands of posts is not even a volume, use MyISAM and the correct indexes. Also show the query you are making to the database.

Answer 2

In short, by trial and error, this turned out to be the most optimal. The time depends mainly on the number of posts in a particular category, and almost does not depend on the page that the user clicks on.

SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, e.news_read, e.allow_rate, e.rating, e.likes, e.dislikes, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason 
FROM `dle_post` AS p 
JOIN ( 
    SELECT t2.id FROM `dle_post` AS t2 
        INNER JOIN `dle_category_map` AS cm ON ( cm.news_id = t2.id AND cat_id = '65') 
    WHERE t2.approve=1 AND date < (NOW() + INTERVAL 0 MINUTE) 
    ORDER BY fixed desc, date DESC 
    LIMIT 1536,32 
) AS j USING (id) 
LEFT JOIN dle_post_extras e ON (p.id=e.news_id)

Query execution time: 25.4ms
The fattest category.