Есть таблица пользователей, фруктов и таблица "отношений":
CREATE TABLE IF NOT EXISTS `relations` (
`user_id` int(11) unsigned NOT NULL,
`fruit_id` int(11) unsigned NOT NULL,
UNIQUE KEY `xox` (`fruit_id`,`user_id`),
KEY `uid` (`user_id`,`fruit_id`)
) ENGINE=InnoDB;
Fruit relations:
user_id, fruit_id
1 1
1 2
1 5
2 1
2 2
2 4
3 3
3 1
Требуется сравнить фрукты пользователя (id: 1) с фруктами остальных пользователей. Узнать у кого из них наибольшее число совпадений и отсортировать в порядке убывания:
user_id, count
1 3
2 2
3 1
Запрос:
SELECT user_id, COUNT(*) AS count FROM relations
WHERE fruit_id IN (SELECT fruit_id FROM relations WHERE user_id=1)
GROUP BY user_id
HAVING count>=2
2 - минимальное кол-во совпадений, на будущее.
Почитал про оптимизацию с оператором IN (
Optimizing Subqueries with EXISTS Strategy), решел "оптимизировать":
SELECT user_id, COUNT(*) AS count FROM relations r
WHERE EXISTS (SELECT 1 FROM relations WHERE user_id=1 AND r.fruit_id=fruit_id)
GROUP BY user_id
HAVING count>=2
Всё вроде отлично, explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY r index NULL uid 8 NULL 15 Using where; Using index
2 DEPENDENT SUBQUERY relations eq_ref xox,uid xox 8 r.fruit_id,const 1 Using where; Using index
Но, нужно же отсортировать в порядке убывания. Если бы данных было мало и не нужен бы был постраничный вывод, то легко можно было бы отсортировать с помощью языка программирования.
Поэтому пришла такая простая идея:
SELECT x.user_id, x.count
FROM (
SELECT user_id, COUNT(*) AS count FROM relations r
WHERE EXISTS (SELECT 1 FROM relations WHERE user_id=1 AND r.fruit_id=fruit_id)
GROUP BY user_id
HAVING count>=2
) x
ORDER BY x.count DESC
Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using filesort
2 DERIVED r index NULL uid 8 NULL 15 Using where; Using index
3 DEPENDENT SUBQUERY relations eq_ref xox,uid xox 8 r.int_id 1 Using index
У меня, собственно, вопросы:
1. Опасен ли этот filesort? Если потом в таблице будет, скажем, 50 миллионов полей или больше?
2. Можно ли как-то сделать то, что я хочу без using temporary table / using filesort?
Спасибо.
UPD:
Не назвал бы это тестированием, просто заполнил таблицу:
20k записей, в среднем 0.05 сек
120k - 0.20 сек
1100k - 2.9 сек.
Да уж, совсем не радует. Можно было бы изменить структуру таблиц, но как, при таком подсчете и сортировке - не понимаю. Есть предложения, как ещё это можно реализовать?
Весь смысл в том, чтобы сначала показать наибольшие совпадения..
Средний
