Как сделать выборку из mysql базы записей и таблицы связей с категориями?

Question

[stikname]

День добрый господа, подскажите следующий вопрос, как реализовать выборку из БД следующего вида: http://sqlfiddle.com/#!9/f62496/2

Что мы имеем:
1. Таблица записей
2. Таблица категорий
3. Таблица связи записей с категориями

Почти у всех записей по несколько категорий.

Необходимо реализовать 3 максимально быстрых MySQL запроса и проставить индексы:
1. Выборка 10 записей с определенной категорией, например 2, сортировка по полю date и условие date <= 1491502307 AND approve = 1
2. Выборка 10 записей в которых присутствуют одновременно три категории, например 2,4,6 (количество категорий может быть разное), с сортировкой по убыванию количества совпадений. Условие (date <= 1491502307 AND approve = 1) пример: sqlfiddle.com/#!9/aa8497/4. В данном примере выборка производится по трем категориям (может быть и более) и записи из бд выводятся сортируясь по количеству совпадений, по убывающей. Это максимум что я смог сделать.
3. Какие индексы нужно проставить для ускорения выборки ?

Решения есть, но кажется что они далеко не идеальные.

Answer 1

[entermix]

1.

SELECT `post`.* FROM `post` JOIN `post2cat` ON (`post2cat`.`id_post` = `post`.`id`)  WHERE `post2cat`.`id_category` = 2 AND `post`.`date` <= 1491502307 AND `post` .`approve` = 1 LIMIT 10;

2.

SELECT `post`.* FROM `post` JOIN `post2cat` ON (`post2cat`.`id_post` = `post`.`id`)  WHERE `post2cat`.`id_category` IN (2,4,6) AND `post`.`date` <= 1491502307 AND `post` .`approve` = 1 LIMIT 10;

OR

SELECT `post`.* FROM `post` JOIN `post2cat` ON(`post2cat`.`id_post` = `post`.`id`) WHERE (`post2cat`.`id_category` = 2 OR `post2cat`.`id_category` = 4 OR `post2cat`.`id_category` = 6) AND `post`.`date` <= 1491502307 AND `post` .`approve` = 1 LIMIT 10;

3. Индексы для post2cat:

-- Индексы таблицы `post2cat`

ALTER TABLE `post2cat`
  ADD PRIMARY KEY (`id_post`,`id_category`),
  ADD KEY `fk_id_post` (`id_post`) USING BTREE,
  ADD KEY `fk_id_category` (`id_category`) USING BTREE;

--
-- Ограничения внешнего ключа таблицы `post2cat`
--
ALTER TABLE `post2cat`
  ADD CONSTRAINT `post2cat_ifbk_1` FOREIGN KEY (`id_post`) REFERENCES `post` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post2cat_ifbk_2` FOREIGN KEY (`id_category`) REFERENCES `category` (`id`) ON DELETE CASCADE;

Comments

[stikname]

Все три варианта выводят дубли, не уникальные записи :( . Вот например как отрабатывает самый первый вариант: sqlfiddle.com/#!9/f14a6/19

[entermix]

stikname: точно, обновил ответ

P.S. поле ID в таблице post2cat - лишнее

[stikname]

entermix: первый вроде как отлично отрабатывает. Сейчас проверю на рабочем проекте с большой базой.
Второй почему то дублирует всё равно: sqlfiddle.com/#!9/2d106f/2

И я похоже не упомянул главное про второе, вот пример: sqlfiddle.com/#!9/aa8497/4
В данном примере выборка производится по трем категориям (может быть и более) и записи из бд выводятся сортируясь по количеству совпадений, по убывающей. Это максимум что я смог сделать. Как считаете, реально более оптимизированный запрос применить к такой задаче?

P.S. Надо основной пост исправить, а то не совсем верно задачу описал.

[entermix]

stikname:

Второй почему то дублирует всё равно: sqlfiddle.com/#!9/2d106f/2

Ну так пост с ID 5 входит, как в категорию с ID 2, так и в категорию с ID 4, поэтому дублируется.

Вы можете использовать DISTINCT, чтобы убрать дубликаты

https://dev.mysql.com/doc/refman/5.7/en/distinct-o...

И я похоже не упомянул главное про второе...

А зачем Вам это? Собственно я не такой уж специалист по оптимизации, но быстрее будет, если использовать WHERE = вместо WHERE IN, но если это экономия на спичках, то я выбираю удобство использования.

[stikname]

entermix:

Ну так пост с ID 5 входит, как в категорию с ID 2, так и в категорию с ID 4, поэтому дублируется.

Вы можете использовать DISTINCT, чтобы убрать дубликаты

Нужны ведь уникальные записи) DISTINCT, да, может быть, но тогда количество записей указанное в LIMIT будет сокращаться в зависимости от того сколько дублей он уберет.
Собралась не малая коллекция вариантов, нужно всё оттестировать на скорость выборки.

А зачем Вам это?

На сайте есть некоторые записи имеющие N категорий, этот запрос служит для подбора прочих записей, с максимально соответствующими категориями.

[entermix]

Нужны ведь уникальные записи) DISTINCT, да, может быть, но тогда количество записей указанное в LIMIT будет сокращаться в зависимости от того сколько дублей он уберет.

Почему Вы так думаете? LIMIT - максимальное количество записей, которые должен вернуть запрос, а не обработать.

[stikname]

Почему Вы так думаете? LIMIT - максимальное количество записей, которые должен вернуть запрос, а не обработать.

Да, вы правы: sqlfiddle.com/#!9/c35cf3/4
Не знал как на самом деле работает DISTINCT

Дубли убрали, но сортировка то всё равно не по убыванию.

[entermix]

stikname: так тут sqlfiddle.com/#!9/c35cf3/4 и нету сортировки, добавьте ORDER BY для нужного поля.

[stikname]

entermix: тут конечно нет. Я про сортировку как в этом моем варианте: sqlfiddle.com/#!9/aa8497/4
поле cnt

[entermix]

stikname:

Да, вы правы: sqlfiddle.com/#!9/c35cf3/4
Не знал как на самом деле работает DISTINCT

Вы так же можете использовать оператор GROUP BY

тут конечно нет. Я про сортировку как в этом моем варианте: sqlfiddle.com/#!9/aa8497/4

Я вижу, что там корректно срабатывает сортировка: 3,3,2,1,1

Вы хотите в этот запрос (sqlfiddle.com/#!9/c35cf3/4) добавить количество? Это уже совсем другой вопрос, мы отошли от темы.