Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737 Форумы портала PHP.SU :: Аналог list() для mysql [2]
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
Помог: 2 раз(а)
Цитата:
поскольку в данном случае два (или же три - не суть важно) JOIN по внешнему ключу неоспоримо более быстры, чем два DEPENDED SUBQUERY в запросе по таблице, содержащей все данные форума.
А вот и неправда!
Я провел сравнительные тесты, и выяснилось, что DEPENDED SUBQUERY быстрее JOIN!!
А началось все с того что вчера вечером fiddle все таки загрузил вашу работу и я увидел запрос с join-ами, решил на локалхосте выяснить, правильно ли он работает. Сначала свой запрос сделал, потом вставил ваш. Отработал он верно, но я заметил что ваш запрос медленнее на пару тысячных секунды. Я подумал что возможно он медленнее работает на маленькой таблице, потому что у него условий больше, групировка, хевинг.. и решил провести сравнительные тесты
mysql>SELECT f.id,f.type,(SELECT COUNT(*)FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS posts,(SELECT COUNT(DISTINCT(f0.topic_id))FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS topics FROM test.Forum f WHERE f.type ='forum';
+----+-------+-------+--------+
| id | type | posts | topics |
+----+-------+-------+--------+
| 1 | forum | 1630 | 3 |
| 2 | forum | 1717 | 5 |
| 15 | forum | 1677 | 2 |
+----+-------+-------+--------+
3 rows INSET(0.02 sec)
mysql>SELECT forum.id, forum.type, COUNT(DISTINCT forum_posts.id)AS posts_count, COUNT(DISTINCT forum_topics.topic_id)AS topics_count FROM test.Forum forum LEFTJOIN test.Forum AS forum_posts ON forum.id=forum_posts.forum_id AND forum_posts.type='post'LEFTJOIN test.Forum AS forum_topics ON forum.id=forum_topics.forum_id AND forum_topics.type='post'GROUPBY forum.topic_id HAVING posts_count>0;
+----+-------+-------------+--------------+
| id | type | posts_count | topics_count |
+----+-------+-------------+--------------+
| 1 | forum | 1630 | 3 |
| 2 | forum | 1717 | 5 |
| 15 | forum | 1677 | 2 |
+----+-------+-------------+--------------+
3 rows INSET(23.41 sec)
Увы и ах Я сейчас добавлю топиков и расширю таблицу до 10 000 записей и еще раз проведу тест. Но даже текущий результат позволяет мне сделать вывод что подзапросами все-же можно и даже нужно пользоваться (например в моем частном случае)
З.Ы. индексы расставлены как и в fiddle
EuGen
Отправлено: 12 Февраля, 2013 - 11:22:58
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Приведите, пожалуйста, SHOW CREATE TABLE исходной таблицы и EXPLAIN для обоих SELECT-запросов. Ну и версию сервера заодно. Никогда не было и не может быть, чтобы вложенный запрос с зависимостью был быстрее JOIN по ключу. Подозреваю, что где-то Вы все же недосмотрели (23 сек. из таблицы всего в 5000 строк при JOIN по ключу? Не верю).
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 12 Февраля, 2013 - 11:39:48
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
mysql>EXPLAINSELECT f.id,f.type,(SELECT COUNT(*)FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS posts,(SELECT COUNT(DISTINCT(f0.topic_id))FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS topics FROM test.Forum f WHERE f.type ='forum';
mysql>EXPLAINSELECT forum.id, forum.type, COUNT(DISTINCT forum_posts.id)AS posts_count, COUNT(DISTINCT forum_topics.topic_id)AS topics_count FROM test.Forum forum LEFTJOIN test.Forum AS forum_posts ON forum.id=forum_posts.forum_id AND forum_posts.type='post'LEFTJOIN test.Forum AS forum_topics ON forum.id=forum_topics.forum_id AND forum_topics.type='post'GROUPBY forum.topic_id HAVING posts_count>0;
- однако тип таблицы у меня InnoDB, а версия сервера 5.5 (это, однако, более чем странно, что планы запросов у нас различные, так как по существу разницы в реализации JOIN по ключу и/или подзапросов между 5.1 и 5.5 кардинальных - нет.).
Мы можем видеть число строк - а именно 1234 при объединении. Происходит это по причине:
- то есть крайне низкая селективность ключа. Оно и понятно - данные "заполнены" скриптом автоматически (у меня по 2000 тысячи сообщений в 3-4 темах), стало быть в данном случае подзапрос дает выигрыш. Однако же в реальной ситуации кардинальное число ключа будет много лучше (я бы даже сказал, в корне лучше), и в общем случае прирост от JOIN будет куда выше.
Результат селективности можно чуть-чуть улучшить, добавив:
Я думаю что MyISAM специально создан для таких извращений
Кстати по поводу извращений
Возвращаясь к первому посту хочу отметить что есть способ одним подзапросом вытащить оба значения:
(SELECT CONCAT_WS(':',COUNT(*),COUNT(DISTINCT(f0.topic_id)))FROM Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS posts_&_topics,
AS topics
FROM Forum f
WHERE f.type ='forum'
Врятли это поможет выиграть в производительности, ведь потом придется делать explode или substr какой-нибудь. А можно внутри запроса результат подзапроса присвоить переменной, а потом над переменной SUBSTRING_INDEX
EuGen
Отправлено: 12 Февраля, 2013 - 12:55:39
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Способ, безусловно, есть, но если представить, что таких значений нужно получить 5-6, то это уже не кажется хорошей затеей.
Anguis пишет:
Скорее всего это из-за MyISAM. InnoDB и MyISAM - разные движки, под различные цели. В InnoDB некоторым данным и такой запрос отлично сработает:
Storage-engine не должен влиять, строго говоря, на допустимое множество SQL-операций. Это в теории. Но на практике - да, есть вещи, которые не удастся сделать с InnoDB - как, к примеру, полнотекстовые индексы (что, впрочем, исправляют в версии 5.6), или, напротив, в MyISAM - как, например, внешние ключи.
Способ же с JOIN, повторюсь, покажет хорошие результаты при рабочей селективности ключей.
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 12 Февраля, 2013 - 12:56:13
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
Помог: 2 раз(а)
Цитата:
Оно и понятно - данные "заполнены" скриптом автоматически (у меня по 2000 тысячи сообщений в 3-4 темах), стало быть в данном случае подзапрос дает выигрыш. Однако же в реальной ситуации кардинальное число ключа будет много лучше (я бы даже сказал, в корне лучше), и в общем случае прирост от JOIN будет куда выше.
Хотите сказать что если модифицировать скрипт, который будет добавлять 1топик со 100 постами рандомно (эмуляция реального форума) и провести тест, результаты изменятся?
Я щас попробую
EuGen
Отправлено: 12 Февраля, 2013 - 12:58:41
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Нужно, чтобы был разброс тем по форумам и сообщений по темам. То есть если есть 8000 записей, то 1 форум с 4 темами по 2000 сообщений - это плохая селективность, а 10 форумов с 80 темами по 10 сообщений в каждой - хорошая ("рабочая", и, кроме того, логически приближенная к реальной ситуации, когда форумов не очень много, сообщений в теме - среднее количество, а вот самих тем - выше среднего).
Так что - да,
Anguis пишет:
результаты изменятся?
- должны измениться. Правда, должна соблюдаться целостность форума (то есть наличие соответствующих forum_id/topic_id для id, как это было бы в реальной ситуации, а не просто хаотичный набор случайных данных)
Еще, я, думаю, понял, что может ухудшать производительность JOIN в данном случае - ведь по сути подзапрос, хоть и вычисляемый, но делает соотношение 1:1 (по первичному ключу), тогда как JOIN сделает отображение 1:(N/C) (где C - кардинальное число ключа, а N - общее число строк). Пока что я думаю, что я вообще неудачно сделал запрос с JOIN - поскольку идет обратное соотношение, которое из очевидной формулы выше - выходит удачным лишь при селективности ключа, близкой к максимальной.
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 12 Февраля, 2013 - 14:09:46
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
mysql>SELECT f.id,f.type,(SELECT COUNT(*)FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS posts,(SELECT COUNT(DISTINCT(f0.topic_id))FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS topics FROM test.Forum f WHERE f.type ='forum';
+------+-------+-------+--------+
| id | type | posts | topics |
+------+-------+-------+--------+
| 1 | forum | 2666 | 11 |
| 2 | forum | 2740 | 16 |
| 15 | forum | 2706 | 10 |
| 5028 | forum | 954 | 15 |
| 5029 | forum | 961 | 16 |
+------+-------+-------+--------+
5 rows INSET(0.05 sec)
mysql>SELECT forum.id, forum.type, COUNT(DISTINCT forum_posts.id)AS posts_count, COUNT(DISTINCT forum_topics.topic_id)AS topics_count FROM test.Forum forum LEFTJOIN test.Forum AS forum_posts ON forum.id=forum_posts.forum_id AND forum_posts.type='post'LEFTJOIN test.Forum AS forum_topics ON forum.id=forum_topics.forum_id AND forum_topics.type='post'GROUPBY forum.topic_id HAVING posts_count>0;
+------+-------+-------------+--------------+
| id | type | posts_count | topics_count |
+------+-------+-------------+--------------+
| 1 | forum | 2666 | 11 |
| 2 | forum | 2740 | 16 |
| 15 | forum | 2706 | 10 |
| 5028 | forum | 954 | 15 |
| 5029 | forum | 961 | 16 |
+------+-------+-------------+--------------+
5 rows INSET(1 min 6.23 sec)
Предыдущая таблица была расширена. Возможно, результат изменился бы если бы таблица изначально так формировалась. В любом случае в данном конкретном случае для меня очевиден выбор в пользу подзапросов.
EuGen
Отправлено: 12 Февраля, 2013 - 14:18:01
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Да, я нашел, в чем ошибался - в данном случае. Я не учел, собственно, то, о чем уже написал выше (JOIN по ключу даст присоединение N/C строк к каждой последующей, то есть 1*(N/C)*(N/C) строк в общем случае, то есть при селективности, например, 0.1 это будет 100-кратное увеличение числа строк).
А сделал я так, потому что упустил из виду собственно само устройство таблицы (точнее, упустил детали).
- такой запрос будет реализовывать связь 1:1 по первичному ключу и потому выполнится быстро, дав нужные данные. JOIN оказался (но это - частность, по-хорошему он нужен в общем случае) вовсе не нужен.
Этот запрос имеет отличный EXPLAIN:
По результатам я проверил, что он выполняет задачу, которая должна им решаться.
Моя ошибка в том, что я изначально искал вариант с JOIN, которого можно было и не делать вовсе - но в общем случае JOIN использовать для подобных нужно (здесь - не нужно, потому что это своего рода таблица "сама в себе" и содержит достаточно данных для решения задачи без соединения даже с самой собой, что в общем случае не будет верно)
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 12 Февраля, 2013 - 18:26:51
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
Помог: 2 раз(а)
Верно, запрос работает быстрее значительно и успешно считает топики и посты, однако вот беда, основную информацию он возвращает не ту...
mysql>SELECT f.id,f.type,(SELECT COUNT(*)FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS posts,(SELECT COUNT(DISTINCT(f0.topic_id))FROM test.Forum f0 WHERE f0.forum_id = f.id AND f0.type ='post')AS topics FROM test.Forum f WHERE f.type ='forum';
+------+-------+-------+--------+
| id | type | posts | topics |
+------+-------+-------+--------+
| 1 | forum | 2666 | 11 |
| 2 | forum | 2740 | 16 |
| 15 | forum | 2706 | 10 |
| 5028 | forum | 954 | 15 |
| 5029 | forum | 961 | 16 |
+------+-------+-------+--------+
5 rows INSET(0.06 sec)
mysql>SELECT id, type, COUNT(DISTINCT id)AS posts_count, COUNT(DISTINCT topic_id)AS topics_count FROM test.Forum WHERE forum_id GROUPBY forum_id;+------+------+-------------+--------------+
| id | type | posts_count | topics_count |
+------+------+-------------+--------------+
| 7 | post | 2666 | 11 |
| 3 | post | 2740 | 16 |
| 16 | post | 2706 | 10 |
| 5030 | post | 954 | 15 |
| 5031 | post | 961 | 16 |
+------+------+-------------+--------------+
5 rows INSET(0.01 sec)
Он выбирает посты, а не форумы. Из строки поста я название форума не вытяну
EuGen
Отправлено: 12 Февраля, 2013 - 19:11:06
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Anguis
Ну так и выбирайте forum_id, а не id. Группировка же по нему идет. Я немного увлекся исследованием JOIN и селективности индексов и упустил это из виду. Благодарю за любопытную задачу, кстати. Редко бывает так, когда есть что исследовать - а здесь, казалось бы, и случай прост, а на самом деле есть не очевидные подводные камни.
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 12 Февраля, 2013 - 20:15:57
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
Помог: 2 раз(а)
EuGen, дело не в том, выбирать forum_id или id, дело в том что вся основная текстовая информация содержится в поле json с type='forum' и нужно выбирать именно его, а не id или forum_id. В вашем запросе дело в WHERE который, я так понимаю, (ни разу не встречал параметр в where без сравнивания) не должен являться NULL, что позволяет выбрать всю таблицу, совершить подсчет по всей таблице, но не выбрать записи c type=forum и необходимым json-текстом, без использования join. По крайней мере WHERE id , WHERE type=forum и т.п. манипуляции ни к чему не привели ) (Добавление)
Вот такой запрос то что нужно и быстро
mysql>SELECT f.id, f.type, COUNT(DISTINCT a.id)AS posts_count, COUNT(DISTINCT a.topic_id)AS topics_count FROM test.Forum a JOIN test.Forum f ON f.id = a.forum_id WHERE a.forum_id GROUPBY a.forum_id;
+------+-------+-------------+--------------+
| id | type | posts_count | topics_count |
+------+-------+-------------+--------------+
| 1 | forum | 2666 | 11 |
| 2 | forum | 2740 | 16 |
| 15 | forum | 2706 | 10 |
| 5028 | forum | 954 | 15 |
| 5029 | forum | 961 | 16 |
+------+-------+-------------+--------------+
5 rows INSET(0.01 sec)
EuGen
Отправлено: 12 Февраля, 2013 - 21:11:23
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
Anguis пишет:
В вашем запросе дело в WHERE который, я так понимаю, (ни разу не встречал параметр в where без сравнивания) не должен являться NUL
Нет. Суть дела в том, что если он будет равным нулю, то мы учтем форумы самих в себя (то есть те записи, у которых forum_id=0). По поводу отсутствия сравнения - в данном случае WHERE forum_id!=0 и WHERE forum_id - эквивалентны, второе при приведении к булеву даст то же условие.
Anguis пишет:
дело в том что вся основная текстовая информация содержится в поле json с type='forum' и нужно выбирать именно его
Вот теперь понятно. Тогда - да, JOIN, Вы опередили меня, но тем и лучше - собственно, мы и пришли окончательно к JOIN, который является оптимальным в данной ситуации.
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Anguis
Отправлено: 14 Февраля, 2013 - 00:57:27
Частый гость
Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012
Помог: 2 раз(а)
EuGen,
Цитата:
Благодарю за любопытную задачу, кстати
Хотите еще одну? Хотелось бы оптимизировать нижеследующий запрос, если это возможно. На той-же таблице в 10000 записей он отрабатывает за 0.07 сек. В принципе, этого достаточно, но если возможно такой запрос оптимизировать, было бы очень хорошо!!)
Расскажу что он делает - он выбирает темы форума в порядке последнего добавленного сообщения с информацией о авторе, создавшем тему и пользователе, написавшем последнее сообщение. Подзапрос используется для инвертирования таблицы, иначе mysql упорно не желает выбирать последние сообщения (берет первое найденное).
Все гости форума могут просматривать этот раздел. Только зарегистрированные пользователи могут создавать новые темы в этом разделе. Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.