Как грамотно связать посты и категории (товар и категории, если угодно)?

Question

[AlikDex]

Вариант со связующей таблицей аля:
Post
post_id (PK)
title

Category
category_id (PK)
title

Post_Category
post_id (PK)
category_id (PK)

конечно хороший, но при десятках тысяч постов он медленный.
БД: мускул, InnoDB

Comments

[Сергей Сенкевич]

А как именно у вас определен PK на Post_Category, есть ли еще какие-то индексы на Post_Category и какой именно запрос тормозит?

Answer 1

[lyeskin]

Глупости, десятки тысяч постов это не объем даже, используйте MyISAM и правильные индексы. Также покажите запрос, который вы делаете к БД.

Comments

[Станислав Макаров]

Я бы еще на типы данных глянул. А то народ иногда varchar ставит в качестве id, а потом жалуется, что запросы медленные.

[AlikDex]

Какой из этих двух запросов наименее ресурсоемкий?
запросы с такой структурой бд.

в дополнение к ним простой запрос

EXPLAIN SELECT p.id
FROM `dle_category_map` AS t1 USE INDEX (news_id)
LEFT JOIN `dle_post` AS p  USE INDEX (cat_posts) ON (t1.news_id = p.id)
WHERE  t1.cat_id=65 AND approve=1 
ORDER BY fixed DESC
LIMIT 1536,32

explain:

| id | select_type | table | type   | possible_keys | key       | key_len | ref                      | rows | Extra       |
|----|-------------|-------|--------|---------------|-----------|---------|--------------------------|------|-------------|
| 1  | SIMPLE      | p     | ref    | cat_posts     | cat_posts | 1       | const                    | 8887 | Using where |
| 1  | SIMPLE      | t1    | eq_ref | news_id       | news_id   | 8       | hdvideoscript.p.id,const | 1    | Using index |

занимает чуууть чуть меньше времени и составляет в среднем 90мс

[AlikDex]

Станислав Макаров: я конечно новичек в SQL, но не настолько ))

[lyeskin]

AlikDex: Ну первое - попробуйте сменить на MyISAM , если нет жесткой необходимости в InnoDB. Второе - попробуйте поменять местами approve и cat_id в условии WHERE. Третье - 90 мс не так уж много для запроса с двумя условиями WHERE, одним джойном и (самое главное) лимитом и оффсетом. Так же добавьте индекс для поля approve.

[lyeskin]

AlikDex: И еще, для данного запроса попробуйте сделать обратный составной индекс для many-to-many таблицы - (cat_id, post_id) вместо (post_id, cat_id)

[AlikDex]

lyeskin: все это есть, вариации со сменой мест и перестройкой индексов испробованы всевозможными способами. Я ж не просто так вопрос задал. 90мс на 8.8к постов, цифра увеличится на большем количестве постов.

[lyeskin]

AlikDex: Я вам с самого начала говорю - пробуйте MyISAM. Вы в упор игнорируете этот совет ? :)

[AlikDex]

c MyIsam также скачет между 70мс и 110мс

[Станислав Макаров]

AlikDex а у вас вообще где СУБД сейчас крутится? На хостинге? есть соображение, что 90мс не так уж плохо

[AlikDex]

Станислав Макаров: на сервере ага. 90мс это много, с конца таблицы, тем более что данных очень мало. Мне интересно, как работают магазины автозапчастей, например? Категорий у них несколько десятков тысяч обычно, а товаров вообще миллионы. Как все это связывается и работает очень шустро?

[Alex Safonov]

Если для вас так критична скорость, то можете прибегнуть к денормализации (если конечно у вас пост привязан только к одной таблице).

Post
post_id (PK)
title
bound_to_category_id

[lyeskin]

Alex Safonov: ага, или MongoDB

[Станислав Макаров]

> Как все это связывается и работает очень шустро?
Вы неправильно прикидываете скорость выполнения запроса. Всегда есть постоянные издержки, например на синтаксический разбор и построение плана. Если вы внутри приложения будете использовать prepared statements, запрос будет выполняться побыстрее. Это раз.
Два - это то, что при наличии индексов длительность выполнения запросов растет алгоритмически. Это достигается за счет использования разновидностей деревьев в индексах, например B+-дерева. Т.е. если у вас количество записей вырастет в 100 раз, время запроса ни в коем случае не должно вырасти в 100 раз, оно даже в 2 раза не должно вырасти.

[Станислав Макаров]

AlikDex для пинга
а что за сервер-то? Это ж и от дисковой подсистемы тоже зависит. Если сервачок за 200 рублей, и железо жестко оверселлится хостером, 90мс это просто отлично.

[AlikDex]

Станислав Макаров: 2процессорный по 8 ядер на камне, xeon'ы, далеко за 200 баксов )
Правда там народу шарится, но запросов в среднем всего около 16тысяч в минуту

Answer 2

[AlikDex]

Короче методом проб и ошибок наиболее оптимальным получилось это. Время зависит в основном от количества постов в конкретной категории, и почти не зависит от страницы, которую кликает юзер.

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)

Время выполнения запроса:25,4ms
Самая жирная категория.