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]

 PHP.SU

 Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (2): « 1 [2]   

> Без описания
Поиск в теме | Версия для печати
Anguis
Отправлено: 12 Февраля, 2013 - 11:08:50
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
Цитата:
поскольку в данном случае два (или же три - не суть важно) JOIN по внешнему ключу неоспоримо более быстры, чем два DEPENDED SUBQUERY в запросе по таблице, содержащей все данные форума.


А вот и неправда!
Я провел сравнительные тесты, и выяснилось, что DEPENDED SUBQUERY быстрее JOIN!!
А началось все с того что вчера вечером fiddle все таки загрузил вашу работу и я увидел запрос с join-ами, решил на локалхосте выяснить, правильно ли он работает. Сначала свой запрос сделал, потом вставил ваш. Отработал он верно, но я заметил что ваш запрос медленнее на пару тысячных секунды. Я подумал что возможно он медленнее работает на маленькой таблице, потому что у него условий больше, групировка, хевинг.. и решил провести сравнительные тесты

Таблицу скриптом расширил до 5 тысяч записей:
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. mysql> SELECT COUNT(*) FROM test.Forum;
  3. +----------+
  4. | COUNT(*) |
  5. +----------+
  6. |     5027 |
  7. +----------+
  8. 1 row IN SET (0.00 sec)
  9.  
  10. 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';
  11. +----+-------+-------+--------+
  12. | id | type  | posts | topics |
  13. +----+-------+-------+--------+
  14. |  1 | forum |  1630 |      3 |
  15. |  2 | forum |  1717 |      5 |
  16. | 15 | forum |  1677 |      2 |
  17. +----+-------+-------+--------+
  18. 3 rows IN SET (0.02 sec)
  19.  
  20. 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    LEFT JOIN    test.Forum AS forum_posts       ON forum.id=forum_posts.forum_id AND forum_posts.type='post'    LEFT JOIN    test.Forum AS forum_topics      ON forum.id=forum_topics.forum_id AND forum_topics.type='post'  GROUP BY    forum.topic_id  HAVING    posts_count>0;
  21. +----+-------+-------------+--------------+
  22. | id | type  | posts_count | topics_count |
  23. +----+-------+-------------+--------------+
  24. |  1 | forum |        1630 |            3 |
  25. |  2 | forum |        1717 |            5 |
  26. | 15 | forum |        1677 |            2 |
  27. +----+-------+-------------+--------------+
  28. 3 rows IN SET (23.41 sec)
  29.  


Увы и ах Улыбка Я сейчас добавлю топиков и расширю таблицу до 10 000 записей и еще раз проведу тест. Но даже текущий результат позволяет мне сделать вывод что подзапросами все-же можно и даже нужно пользоваться (например в моем частном случае)

З.Ы. индексы расставлены как и в fiddle
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 11:22:58
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Приведите, пожалуйста, SHOW CREATE TABLE исходной таблицы и EXPLAIN для обоих SELECT-запросов. Ну и версию сервера заодно. Никогда не было и не может быть, чтобы вложенный запрос с зависимостью был быстрее JOIN по ключу. Подозреваю, что где-то Вы все же недосмотрели (23 сек. из таблицы всего в 5000 строк при JOIN по ключу? Не верю).


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 11:39:48
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. mysql> SHOW CREATE TABLE test.Forum;
  3. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | TABLE | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  5. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | Forum | CREATE TABLE `Forum` (
  7.   `id` int(11) NOT NULL AUTO_INCREMENT,
  8.   `user_id` int(11) NOT NULL,
  9.   `user_topic` int(11) NOT NULL,
  10.   `type` enum('forum','post') NOT NULL,
  11.   `forum_id` int(11) NOT NULL,
  12.   `topic_id` int(11) NOT NULL,
  13.   `json` longtext NOT NULL,
  14.   `rate` int(11) NOT NULL,
  15.   `new` tinyint(1) NOT NULL,
  16.   PRIMARY KEY (`id`),
  17.   KEY `forum_id` (`forum_id`),
  18.   KEY `topic_id` (`topic_id`),
  19.   KEY `user_topic` (`user_topic`)
  20. ) ENGINE=MyISAM AUTO_INCREMENT=5028 DEFAULT CHARSET=utf8 |
  21. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  22. 1 row IN SET (0.00 sec)
  23.  
  24. mysql> EXPLAIN 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';
  25. +----+--------------------+-------+------+---------------+----------+---------+-----------+------+-------------+
  26. | id | select_type        | TABLE | type | possible_keys | KEY      | key_len | ref       | rows | Extra       |
  27. +----+--------------------+-------+------+---------------+----------+---------+-----------+------+-------------+
  28. |  1 | PRIMARY            | f     | ALL  | NULL          | NULL     | NULL    | NULL      | 5027 | USING WHERE |
  29. |  3 | DEPENDENT SUBQUERY | f0    | ref  | forum_id      | forum_id | 4       | test.f.id |  603 | USING WHERE |
  30. |  2 | DEPENDENT SUBQUERY | f0    | ref  | forum_id      | forum_id | 4       | test.f.id |  603 | USING WHERE |
  31. +----+--------------------+-------+------+---------------+----------+---------+-----------+------+-------------+
  32. 3 rows IN SET (0.00 sec)
  33.  
  34. mysql> EXPLAIN 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    LEFT JOIN    test.Forum AS forum_posts       ON forum.id=forum_posts.forum_id AND forum_posts.type='post'    LEFT JOIN    test.Forum AS forum_topics      ON forum.id=forum_topics.forum_id AND forum_topics.type='post'  GROUP BY    forum.topic_id  HAVING    posts_count>0;
  35. +----+-------------+--------------+------+---------------+----------+---------+---------------+------+----------------+
  36. | id | select_type | TABLE        | type | possible_keys | KEY      | key_len | ref           | rows | Extra          |
  37. +----+-------------+--------------+------+---------------+----------+---------+---------------+------+----------------+
  38. |  1 | SIMPLE      | forum        | ALL  | NULL          | NULL     | NULL    | NULL          | 5027 | USING filesort |
  39. |  1 | SIMPLE      | forum_posts  | ref  | forum_id      | forum_id | 4       | test.forum.id |   12 |                |
  40. |  1 | SIMPLE      | forum_topics | ref  | forum_id      | forum_id | 4       | test.forum.id |   12 |                |
  41. +----+-------------+--------------+------+---------------+----------+---------+---------------+------+----------------+
  42. 3 rows IN SET (0.00 sec)
  43.  

(Добавление)
CODE (htmlphp):
скопировать код в буфер обмена
  1.  
  2. $ uname -a
  3. Linux Python 3.2.18-pclos2.bfs #1 SMP PREEMPT Thu May 24 04:44:50 CEST 2012 i686 i686 i386 GNU/Linux
  4.  
  5.  
  6. Your MySQL connection id is 151
  7. Server version: 5.1.55 PCLinuxOS - MySQL Standard Edition (GPL)
  8.  
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 12:03:56
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Мой план запроса выглядит так
CODE (SQL):
скопировать код в буфер обмена
  1. +----+-------------+--------------+-------+---------------+-----------+---------+---------------------+------+-------+
  2. | id | select_type | TABLE        | type  | possible_keys | KEY       | key_len | ref                 | rows | Extra |
  3. +----+-------------+--------------+-------+---------------+-----------+---------+---------------------+------+-------+
  4. |  1 | SIMPLE      | tbl20130212  | INDEX | NULL          | byTopicid | 5       | NULL                |    1 |       |
  5. |  1 | SIMPLE      | forum_posts  | ref   | byForumId     | byForumId | 5       | test.tbl20130212.id | 1234 |       |
  6. |  1 | SIMPLE      | forum_topics | ref   | byForumId     | byForumId | 5       | test.tbl20130212.id | 1234 |       |
  7. +----+-------------+--------------+-------+---------------+-----------+---------+---------------------+------+-------+

- однако тип таблицы у меня InnoDB, а версия сервера 5.5 (это, однако, более чем странно, что планы запросов у нас различные, так как по существу разницы в реализации JOIN по ключу и/или подзапросов между 5.1 и 5.5 кардинальных - нет.).

Мы можем видеть число строк - а именно 1234 при объединении. Происходит это по причине:
CODE (SQL):
скопировать код в буфер обмена
  1. +-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  2. | TABLE       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment | Index_comment |
  3. +-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  4. | tbl20130212 |          0 | PRIMARY   |            1 | id          | A         |        8649 |     NULL | NULL   |      | BTREE      |         |               |
  5. | tbl20130212 |          1 | byTopicid |            1 | topic_id    | A         |          16 |     NULL | NULL   | YES  | BTREE      |         |               |
  6. | tbl20130212 |          1 | byForumId |            1 | forum_id    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
  7. +-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  8.  

- то есть крайне низкая селективность ключа. Оно и понятно - данные "заполнены" скриптом автоматически (у меня по 2000 тысячи сообщений в 3-4 темах), стало быть в данном случае подзапрос дает выигрыш. Однако же в реальной ситуации кардинальное число ключа будет много лучше (я бы даже сказал, в корне лучше), и в общем случае прирост от JOIN будет куда выше.
Результат селективности можно чуть-чуть улучшить, добавив:
CODE (SQL):
скопировать код в буфер обмена
  1. ALTER TABLE tbl20130212 ADD KEY byForumidType(forum_id, type);

- получаем
CODE (SQL):
скопировать код в буфер обмена
  1. +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  2. | TABLE       | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment | Index_comment |
  3. +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  4. | tbl20130212 |          0 | PRIMARY       |            1 | id          | A         |        8108 |     NULL | NULL   |      | BTREE      |         |               |
  5. | tbl20130212 |          1 | byTopicid     |            1 | topic_id    | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
  6. | tbl20130212 |          1 | byForumId     |            1 | forum_id    | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
  7. | tbl20130212 |          1 | byForumidType |            1 | forum_id    | A         |         188 |     NULL | NULL   | YES  | BTREE      |         |               |
  8. | tbl20130212 |          1 | byForumidType |            2 | type        | A         |         188 |     NULL | NULL   | YES  | BTREE      |         |               |
  9. +-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  10.  

- и, соответственно, план запроса:
CODE (SQL):
скопировать код в буфер обмена
  1. +----+-------------+--------------+-------+-------------------------+---------------+---------+---------------------------+------+-------------+
  2. | id | select_type | TABLE        | type  | possible_keys           | KEY           | key_len | ref                       | rows | Extra       |
  3. +----+-------------+--------------+-------+-------------------------+---------------+---------+---------------------------+------+-------------+
  4. |  1 | SIMPLE      | tbl20130212  | INDEX | NULL                    | byTopicid     | 5       | NULL                      |    1 |             |
  5. |  1 | SIMPLE      | forum_posts  | ref   | byForumId,byForumidType | byForumidType | 7       | test.tbl20130212.id,const | 1013 | USING INDEX |
  6. |  1 | SIMPLE      | forum_topics | ref   | byForumId,byForumidType | byForumidType | 7       | test.tbl20130212.id,const | 1013 |             |
  7. +----+-------------+--------------+-------+-------------------------+---------------+---------+---------------------------+------+-------------+


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 12:42:30
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
Скорее всего это из-за MyISAM. InnoDB и MyISAM - разные движки, под различные цели. В InnoDB некоторым данным и такой запрос отлично сработает:
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT f.*
  3. FROM Forum f
  4. WHERE f.forum_id=$id AND f.type = 'post'
  5. GROUP BY f.topic_id
  6. ORDER BY f.id DESC
  7. LIMIT 30;
  8.  

А с MyISAM нужно извращаться вот так:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT f.*
  2. FROM (SELECT * FROM Forum ORDER BY id DESC) f
  3. WHERE f.forum_id=$id AND f.type = 'post'
  4. GROUP BY f.topic_id
  5. ORDER BY f.id DESC
  6. LIMIT 30;

Я думаю что MyISAM специально создан для таких извращений Улыбка
Кстати по поводу извращений Улыбка
Возвращаясь к первому посту хочу отметить что есть способ одним подзапросом вытащить оба значения:
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT f.*,
  3. (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,
  4. AS topics
  5. FROM Forum f
  6. WHERE f.type = 'forum'
  7.  

Радость Врятли это поможет выиграть в производительности, ведь потом придется делать explode или substr какой-нибудь. А можно внутри запроса результат подзапроса присвоить переменной, а потом над переменной SUBSTRING_INDEX Не понял Ха-ха
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 12:55:39
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Способ, безусловно, есть, но если представить, что таких значений нужно получить 5-6, то это уже не кажется хорошей затеей.
Anguis пишет:
Скорее всего это из-за MyISAM. InnoDB и MyISAM - разные движки, под различные цели. В InnoDB некоторым данным и такой запрос отлично сработает:

Storage-engine не должен влиять, строго говоря, на допустимое множество SQL-операций. Это в теории. Но на практике - да, есть вещи, которые не удастся сделать с InnoDB - как, к примеру, полнотекстовые индексы (что, впрочем, исправляют в версии 5.6), или, напротив, в MyISAM - как, например, внешние ключи.
Способ же с JOIN, повторюсь, покажет хорошие результаты при рабочей селективности ключей.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 12:56:13
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
Цитата:

Оно и понятно - данные "заполнены" скриптом автоматически (у меня по 2000 тысячи сообщений в 3-4 темах), стало быть в данном случае подзапрос дает выигрыш. Однако же в реальной ситуации кардинальное число ключа будет много лучше (я бы даже сказал, в корне лучше), и в общем случае прирост от JOIN будет куда выше.

Хотите сказать что если модифицировать скрипт, который будет добавлять 1топик со 100 постами рандомно (эмуляция реального форума) и провести тест, результаты изменятся?
Я щас попробую Улыбка
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 12:58:41
Post Id


Профессионал


Покинул форум
Сообщений всего: 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 - поскольку идет обратное соотношение, которое из очевидной формулы выше - выходит удачным лишь при селективности ключа, близкой к максимальной.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 14:09:46
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
Сделал:
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. mysql> SELECT COUNT(*) FROM test.Forum;
  3. +----------+
  4. | COUNT(*) |
  5. +----------+
  6. |    10032 |
  7. +----------+
  8. 1 row IN SET (0.00 sec)
  9.  
  10. 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';
  11. +------+-------+-------+--------+
  12. | id   | type  | posts | topics |
  13. +------+-------+-------+--------+
  14. |    1 | forum |  2666 |     11 |
  15. |    2 | forum |  2740 |     16 |
  16. |   15 | forum |  2706 |     10 |
  17. | 5028 | forum |   954 |     15 |
  18. | 5029 | forum |   961 |     16 |
  19. +------+-------+-------+--------+
  20. 5 rows IN SET (0.05 sec)
  21.  
  22. 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    LEFT JOIN    test.Forum AS forum_posts       ON forum.id=forum_posts.forum_id AND forum_posts.type='post'    LEFT JOIN    test.Forum AS forum_topics      ON forum.id=forum_topics.forum_id AND forum_topics.type='post'  GROUP BY    forum.topic_id  HAVING    posts_count>0;
  23. +------+-------+-------------+--------------+
  24. | id   | type  | posts_count | topics_count |
  25. +------+-------+-------------+--------------+
  26. |    1 | forum |        2666 |           11 |
  27. |    2 | forum |        2740 |           16 |
  28. |   15 | forum |        2706 |           10 |
  29. | 5028 | forum |         954 |           15 |
  30. | 5029 | forum |         961 |           16 |
  31. +------+-------+-------------+--------------+
  32. 5 rows IN SET (1 min 6.23 sec)
  33.  


Предыдущая таблица была расширена. Возможно, результат изменился бы если бы таблица изначально так формировалась. В любом случае в данном конкретном случае для меня очевиден выбор в пользу подзапросов.
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 14:18:01
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Да, я нашел, в чем ошибался - в данном случае. Я не учел, собственно, то, о чем уже написал выше (JOIN по ключу даст присоединение N/C строк к каждой последующей, то есть 1*(N/C)*(N/C) строк в общем случае, то есть при селективности, например, 0.1 это будет 100-кратное увеличение числа строк).
А сделал я так, потому что упустил из виду собственно само устройство таблицы (точнее, упустил детали).
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT
  2.   tbl20130212.id,
  3.   tbl20130212.user_id,
  4.   tbl20130212.user_topic,
  5.   tbl20130212.type,
  6.   tbl20130212.forum_id,
  7.   tbl20130212.topic_id,
  8.   COUNT(DISTINCT tbl20130212.id) AS posts_count,
  9.   COUNT(DISTINCT tbl20130212.topic_id) AS topics_count
  10. FROM
  11.   tbl20130212
  12. WHERE
  13.   tbl20130212.forum_id
  14. GROUP BY
  15.   tbl20130212.forum_id

- такой запрос будет реализовывать связь 1:1 по первичному ключу и потому выполнится быстро, дав нужные данные. JOIN оказался (но это - частность, по-хорошему он нужен в общем случае) вовсе не нужен.
Этот запрос имеет отличный EXPLAIN:
CODE (SQL):
скопировать код в буфер обмена
  1. +----+-------------+-------------+-------+---------------+-----------+---------+------+-------+-------------+
  2. | id | select_type | TABLE       | type  | possible_keys | KEY       | key_len | ref  | rows  | Extra       |
  3. +----+-------------+-------------+-------+---------------+-----------+---------+------+-------+-------------+
  4. |  1 | SIMPLE      | tbl20130212 | INDEX | NULL          | byForumId | 5       | NULL | 24441 | USING WHERE |
  5. +----+-------------+-------------+-------+---------------+-----------+---------+------+-------+-------------+

По результатам я проверил, что он выполняет задачу, которая должна им решаться.

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


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 18:26:51
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
Верно, запрос работает быстрее значительно и успешно считает топики и посты, однако вот беда, основную информацию он возвращает не ту...
CODE (SQL):
скопировать код в буфер обмена
  1.  
  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';
  3. +------+-------+-------+--------+
  4. | id   | type  | posts | topics |
  5. +------+-------+-------+--------+
  6. |    1 | forum |  2666 |     11 |
  7. |    2 | forum |  2740 |     16 |
  8. |   15 | forum |  2706 |     10 |
  9. | 5028 | forum |   954 |     15 |
  10. | 5029 | forum |   961 |     16 |
  11. +------+-------+-------+--------+
  12. 5 rows IN SET (0.06 sec)
  13.  
  14. mysql> SELECT id, type, COUNT(DISTINCT id) AS posts_count, COUNT(DISTINCT topic_id) AS topics_count  FROM test.Forum  WHERE forum_id  GROUP BY forum_id;+------+------+-------------+--------------+
  15. | id   | type | posts_count | topics_count |
  16. +------+------+-------------+--------------+
  17. |    7 | post |        2666 |           11 |
  18. |    3 | post |        2740 |           16 |
  19. |   16 | post |        2706 |           10 |
  20. | 5030 | post |         954 |           15 |
  21. | 5031 | post |         961 |           16 |
  22. +------+------+-------------+--------------+
  23. 5 rows IN SET (0.01 sec)
  24.  


Он выбирает посты, а не форумы. Из строки поста я название форума не вытяну Улыбка
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 19:11:06
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Anguis
Ну так и выбирайте forum_id, а не id. Группировка же по нему идет. Я немного увлекся исследованием JOIN и селективности индексов и упустил это из виду. Благодарю за любопытную задачу, кстати. Редко бывает так, когда есть что исследовать - а здесь, казалось бы, и случай прост, а на самом деле есть не очевидные подводные камни.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 12 Февраля, 2013 - 20:15:57
Post Id



Частый гость


Покинул форум
Сообщений всего: 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 и т.п. манипуляции ни к чему не привели )
(Добавление)
Вот такой запрос то что нужно и быстро Улыбка
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. 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  GROUP BY a.forum_id;
  3. +------+-------+-------------+--------------+
  4. | id   | type  | posts_count | topics_count |
  5. +------+-------+-------------+--------------+
  6. |    1 | forum |        2666 |           11 |
  7. |    2 | forum |        2740 |           16 |
  8. |   15 | forum |        2706 |           10 |
  9. | 5028 | forum |         954 |           15 |
  10. | 5029 | forum |         961 |           16 |
  11. +------+-------+-------------+--------------+
  12. 5 rows IN SET (0.01 sec)
  13.  
 
 Top
EuGen Администратор
Отправлено: 12 Февраля, 2013 - 21:11:23
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Anguis пишет:
В вашем запросе дело в WHERE который, я так понимаю, (ни разу не встречал параметр в where без сравнивания) не должен являться NUL

Нет. Суть дела в том, что если он будет равным нулю, то мы учтем форумы самих в себя (то есть те записи, у которых forum_id=0). По поводу отсутствия сравнения - в данном случае WHERE forum_id!=0 и WHERE forum_id - эквивалентны, второе при приведении к булеву даст то же условие.
Anguis пишет:
дело в том что вся основная текстовая информация содержится в поле json с type='forum' и нужно выбирать именно его

Вот теперь понятно. Тогда - да, JOIN, Вы опередили меня, но тем и лучше - собственно, мы и пришли окончательно к JOIN, который является оптимальным в данной ситуации.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Anguis
Отправлено: 14 Февраля, 2013 - 00:57:27
Post Id



Частый гость


Покинул форум
Сообщений всего: 253
Дата рег-ции: Июнь 2012  


Помог: 2 раз(а)
EuGen,
Цитата:
Благодарю за любопытную задачу, кстати
Хотите еще одну? Хотелось бы оптимизировать нижеследующий запрос, если это возможно. На той-же таблице в 10000 записей он отрабатывает за 0.07 сек. В принципе, этого достаточно, но если возможно такой запрос оптимизировать, было бы очень хорошо!!)
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT t . * , COUNT( f.id ) posts, p.json user, a.json author
  3. FROM (
  4. SELECT *
  5. FROM Forum
  6. ORDER BY id DESC
  7. )f
  8. JOIN Pages p ON p.id = f.user_id
  9. JOIN Pages a ON a.id = f.user_topic
  10. JOIN Forum t ON t.id = f.topic_id
  11. WHERE f.forum_id =1
  12. AND f.type = 'post'
  13. GROUP BY f.topic_id
  14. ORDER BY f.id DESC
  15.  


Расскажу что он делает - он выбирает темы форума в порядке последнего добавленного сообщения с информацией о авторе, создавшем тему и пользователе, написавшем последнее сообщение. Подзапрос используется для инвертирования таблицы, иначе mysql упорно не желает выбирать последние сообщения (берет первое найденное).
 
 Top
Поиск в теме | Версия для печати
Страниц (2): « 1 [2]
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« SQL и Архитектура БД »


Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.
  



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB