Xpoint
   [напомнить пароль]

Получение количества записей по выбранным тегам

Метки: [без меток]
2008-05-27 16:41:25 [обр] Евгений[досье]

Исходные данные:

CREATE TABLE `relations` (
  `note` int(11) NOT NULL,
  `tag` int(11) NOT NULL,
  PRIMARY KEY  (`note`,`tag`),
  UNIQUE KEY `tag_note` (`tag`,`note`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Таблица описывает связи между таблицей тегов и таблицей записей. Понятно, что many-to-many.

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

Я всю голову уже сломал. Максимум к чему пришел:

SELECT GROUP_CONCAT(`tag`) as tag_ids
FROM (`relations`)
GROUP BY `note`
HAVING
FIND_IN_SET(24, `tag_ids`) AND
FIND_IN_SET(14, `tag_ids`)

Помогите, если это возможно, сделать подобную выборку проще, ибо нутром чую, при большом количестве записей такой вариант будет медлительным (я уже молчу про построенные на подобном принципе выборки записей из базы)

спустя 6 минут [обр] GRAy(8/259)[досье]
Евгений[досье] Чем не удовлетворяет простой вариант?
select tag, count(disitnct note)
from relations
where tag in (22, 44,12 ....)
group by tag
спустя 45 минут [обр] Евгений[досье]

Немного непонятно я написал. Мне не нужна выборка, мне нужно в итоге запустить select и получить на выходе цифру и все. Причем все указанные теги, по которым производится выборка, должны обязательно присутствовать в каждой записи.

Объясню на примере:
Имеем:

note1 с тегами 11,12,15,20
note2 с тегами 12,59,2
note3 с тегами 11,65,37

Мне нужно получить кол-во записей с метками 11 и 12. Для данного примера это количество равно 1. Т.е. и 11, и 12 должны присутствовать в списке тегов для данной записи. Именно поэтому я выбираю в приведенном выше своем запросе вначале строку с перечисленными через запятую тегами для каждой метке, а затем ищу в ней указанные теги с обязательным присутствием всех указанных тегов в выборке.

Надеюсь, стало более понятно.

спустя 28 минут [обр] GRAy(8/259)[досье]
select note, count(distinct tag)
from relations
where tag in (11,12)
group by note
having count(distinct tag) = 2 -- это количество тегов в списке, которым вы фильтруете начальную запись
Т.к. мы отфильтровали только такие note, у которых есть теги из вашего списка с помощью in (11,12), у сгруппированных по note строк count(distinct tag) будет равен кол-ву тегов в том и только в том случае, если все эти теги к ней приписаны (distinct, в данном случае перестраховка). Соотв. having - выбереть только их.
Если вы хотите ещё дополнительно получать список всех тегов для данной note - надо будет запихивать вышеприведённый селект в подселект.
спустя 37 минут [обр] Евгений[досье]

Волшебство! :) Спасибо.

Если вы хотите ещё дополнительно получать список всех тегов для данной note - надо будет запихивать вышеприведённый селект в подселект.

Т.е. что-то вроде

SELECT text
FROM notes
WHERE id IN (
 SELECT note
 FROM relations
 WHERE tag IN (11,12)
 GROUP BY note
 HAVING COUNT(DISTINCT tag) = 2)

?

спустя 2 часа 40 минут [обр] Александр Галкин(0/211)[досье]
Ещё вариант:
SELECT distinct n.*
FROM notes n
    JOIN relations r1 ON r1.note = n.id AND r1.tag = ?
    JOIN relations r2 ON r2.note = n.id AND r2.tag = ?
спустя 2 часа 5 минут [обр] Евгений[досье]

Александр Галкин[досье], спасибо, тоже вариант.
Самое забавное хотите узнать?

Вот результаты тестирования запросов на компе:

SELECT DISTINCT notes.text FROM notes JOIN relations r1 ON r1.note = notes.id AND r1.tag =14 JOIN relations r2 ON r2.note = notes.id AND r2.tag =24

0.0111 сек.

SELECT text FROM notes WHERE id IN ( SELECT note FROM relations WHERE tag IN ( 14, 24 ) GROUP BY note HAVING COUNT( DISTINCT tag ) =2 )

0.0054 сек.

SELECT notes.text, GROUP_CONCAT(relations.tag) as tag_ids FROM (`notes`) JOIN `relations` ON relations.note = notes.id GROUP BY notes.id HAVING FIND_IN_SET(24, `tag_ids`) AND FIND_IN_SET(14, `tag_ids`)

0.0017 сек.

:-/

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

спустя 58 минут [обр] Алексей Севрюков(7/1292)[досье]
Евгений[досье] и сколько у Вас строк в таблице? Попробуйте прогоните эти же запросы еще раз и сравните время с текущими данными.
спустя 11 часов [обр] Евгений[досье]

В таблице notes 100 записей, relations - 190.
Прогнал несколько раз подряд (уже на рабочем компе, а не дома) результат принципиально не изменился.

SELECT DISTINCT notes.text FROM notes JOIN relations r1 ON r1.note = notes.id AND r1.tag =14 JOIN relations r2 ON r2.note = notes.id AND r2.tag =24

0.0021 сек.

SELECT text FROM notes WHERE id IN ( SELECT note FROM relations WHERE tag IN ( 14, 24 ) GROUP BY note HAVING COUNT( DISTINCT tag ) =2 )

0.0089 сек.

SELECT notes.text, GROUP_CONCAT(relations.tag) as tag_ids FROM (`notes`) JOIN `relations` ON relations.note = notes.id GROUP BY notes.id HAVING FIND_IN_SET(24, `tag_ids`) AND FIND_IN_SET(14, `tag_ids`)

0.0014 сек.

Как видите все равно мой вариант быстрее, но почему-то запрос с вложенным подзапросом выполняется теперь быстрее, чем дома. Не знаю с чем может быть такое связано. Дома мак мини, mysql 5.0.51; на работе pc, mysql 5.0.45. Замеры проводил на одинаковой базе, разве что сегодня добавил еще записей 30.

спустя 9 минут [обр] Евгений[досье]

Но это еще ладно задачка. От следующей у меня просто мозг разрывается. Используется все та же база.
Нужно сформировать меню на тегах вида "тег [кол-во записей по тегу]", при этом выбрать только те теги, количество которых для данной записи максимально. Поясню на примере:

note1, теги: 12, 14, 15
Кол-во тегов 12 во всей базе - 4; 14 - 2; 15 - 7. Нужно оставить только тег 15, с ним ассоциировано бОльшее число записей.

note2, теги: 5, 7, 9
Кол-во тегов 5 во всей базе - 1; 7 - 1 штука; 9 - 1 штука. Значит нужно оставить все теги.

И вот по такии правилам собрав все теги со всех записей нужно сформировать меню. Я решил задачу выборкой из базы:

SELECT GROUP_CONCAT(relations.tag) as tag_ids, GROUP_CONCAT(tags.name) as tag_names FROM (`relations`) JOIN `tags` ON tags.id = relations.tag GROUP BY relations.note

а затем вручную обработкой результатов в php коде для приведения к нужному виду, т.е. определение в цикле кол-ва тегов, максимума для каждой записи, выборке самых крупных и формирование меню. Хочу узнать есть ли более красивый путь? Тут меня даже производительность волнует меньше, т.к. php-кода в тексте много, как и циклов. Если это можно было бы заменить одним-двумя запросами, было бы просто супер!

спустя 47 минут [обр] Алексей Севрюков(7/1292)[досье]

Евгений[досье] Нет, я не вижу что Ваш вариант быстрее. У Вас всего то строчек раз-два и обчелся. Сделайте хотя бы миллион и тестируйте на здоровье. А на таких наборах Вы будете получать "рандомные" цифры. И совсем не факт что на серьезном наборе данных Ваш запрос будет работать так же.

P.S. То, что Вы замеряете, это, простите, не тестирование, а полная ерунда. Никто так не меряет. Вы бы еще в PHP таким образом замерили бы скорость операции умножения.

спустя 1 час 3 минуты [обр] GRAy(8/259)[досье]
Евгений[досье] Нифига не понял всё равно ;) даже с примером. Что значит "меню на тегах"? Как это связано с предыдущим запросом? Напишите-ка лучше как должна выглядеть структура вашего меню.
спустя 3 минуты [обр] Евгений[досье]
А на таких наборах Вы будете получать "рандомные" цифры
Да, они плюс/минус рандомные, но порядок сохраняется! Если вы подумали, что у меня цифры скачут как бешеные, то нифига! Раз десять обновил страницу и все равно получил тот же порядок цифр: 0.0023, 0.0087 и 0.0015 соответственно, плюс/минус 0.0003. А вы, Алексей Севрюков[досье], прежде чем хаять другого, попробовали бы вначале сами потестировать!
спустя 11 минут [обр] GRAy(8/259)[досье]
Евгений[досье] Алексей просто указывал вам, что на подобных объёмах данных производительность селекта зависит в большей степени не от качества его написания, а от прочих факторов - поэтому приводимые вами цифры, ни о чём не говорят. Ваш вариант на сколько-нибудь серьёзном наборе данных начнёт существенно медленнее работать чем другие предложенные. Никто вас не хаял.
спустя 2 минуты [обр] Евгений[досье]

GRAy[досье], с предыдущим запросом уже никак не связано, просто сохранилась структура базы, и чтобы не плодить тем в форуме я продолжил в этой.

Смотрите, есть таблица статей notes (id, text); существует таблица relations (note, tag); и таблица тегов tags(id, name). Каждая статья может иметь несколько тегов. Статьи (notes) и теги (tags) имеют связи через relations.

В первоначальном виде я сделал меню на тегах, вынеся туда все теги в формате "имя (кол-во записей)". Но такое меню получилось просто огромным. Тогда я решил сделать следующим образом - выводить лишь часть тегов. В этот укороченный список попадают лишь те теги, которые имеют для каждой статьи бОльший вес. Вес определяется частотой их встречи во всей базе.

Пример:
Статья имеет следующие теги: дом, белый, путин. Тег дом встречается во всей базе 4 раза, белый - 2, путин - 18. Значит в меню добавляем тег "путин". И т.д. для каждой статьи. Таким образом, выбранными тегами наполняется меню.

Так более понятно? :)

спустя 2 минуты [обр] Евгений[досье]
GRAy[досье], понимаю, соглашусь. Я и сам не был уверен в том, что мой вариант быстрее и был удивлен результатами. Просто не люблю когда грубо отвечают.
спустя 4 минуты [обр] Алексей Севрюков(7/1292)[досье]

Евгений[досье] Вы меня не слышите. Я Вам говорю "Вы не правильно меряете", а Вы мне в ответ "Раз десять обновил страницу и получил тот же порядок цифр". Я Вас не хаял, а всего лишь указал на то, что Ваши методы замеры неправильные и не дают реальной картины скорости выполнения запроса.

P.S. Вам просто хочется думать что Ваш вариант самый красивый и быстрый, поэтому Вы так и меряете.

спустя 22 минуты [обр] Евгений[досье]
Если бы я так думал, то не пришел сюда за помощью.
спустя 2 минуты [обр] GRAy(8/259)[досье]
Алексей[досье] На вкус, как говорится, и цвет ;) все фломастеры разные. Ну нравится человеку его селект - пусть меряет его как хочет. Реальность рано или поздно расставит всё по своим местам.
Евгений[досье] Уже лучше но всё равно несколько непонятно почему меню тегов строится для каждой статьи? К статье уже привязанн один из тегов который будет выбран по его весу - какой в этом смысл?
спустя 22 минуты [обр] Евгений[досье]

GRAy[досье], нет, меню тегов строится для всех статей единое, просто метод оценки попадания того или иного тега в меню описан мною выше. Т.е. из первой статьи будет выбран самый весомый тег, из второй другой, из третьей еще... и т.д и все выбранные теги попадут в главное меню.

Т.е. для каждой статьи делается

SELECT tag FROM relations WHERE note=?

Затем для каждого выбранного тега

SELECT COUNT(note) FROM relations WHERE tag=?

Получаем этакий массив tag => COUNT(tag), из которого выбираем tag (один или несколько) с максимальным значением COUNT(tag).

Все это повторить для каждой записи и затем полученные результаты объединить.
Уф, подробнее некуда :)

спустя 46 минут [обр] GRAy(8/259)[досье]
Ужас ;) Эх не хватает в MySql поддержки with и аналитических функций как в Oracle - так бы этот селект получился бы короче и быстрее.
select distinct rr.tag, w.cnt  
from relation rr 
  join (
    select r.note, max(b.cnt) mxcnt -- для каждой статьи находим её максимальный вес тега
    from relation r join (
      select tag, count(distinct note) cnt 
      from relation 
      group by tag 
    ) b on r.tag = b.tag
  ) w on rr.note = w.note
  join (
    select tag, count(distinct note) cnt 
    from relation 
    group by tag 
  ) bb on bb.tag = rr.tag and bb.cnt = w.mxcnt
спустя 53 минуты [обр] Евгений[досье]
Один group by забыли, да ничего :) Спасибо большое. Пойду читать мануалы: про select в join'е не додумался бы.
Powered by POEM™ Engine Copyright © 2002-2005