ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос пожалуйста
25 сообщений из 39, страница 1 из 2
  1  все
Помогите оптимизировать запрос пожалуйста
    #38626957
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
Есть сущности пользователь (user), вопрос (question), ответ (answer).
Нужно вывести 10 пользователей у которых были самые популярные вопросы или ответы начиная с определенного момента времени.

Сделал так, но время выполнения 20 секунд....
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT DISTINCT b.login 
FROM
( 
 SELECT id_user, question_rating AS rating
 FROM question 
 WHERE creation_date_question > '2013-04-14 23:59:59'
 UNION
 SELECT id_user, answer_rating AS rating
 FROM answer 
 WHERE creation_date_answer > '2013-04-14 23:59:59'
 ORDER BY rating DESC 
 LIMIT 10
) AS a
LEFT JOIN user AS b
ON (a.id_user = b.id_user);



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+----+--------------+------------+--------+---------------+---------+---------+-----------+---------+-----------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref       | rows    | Extra           |
+----+--------------+------------+--------+---------------+---------+---------+-----------+---------+-----------------+
|  1 | PRIMARY      | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL      |      10 | Using temporary |
|  1 | PRIMARY      | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.id_user |       1 |                 |
|  2 | DERIVED      | question   | ALL    | NULL          | NULL    | NULL    | NULL      |  100191 | Using where     |
|  3 | UNION        | answer     | ALL    | NULL          | NULL    | NULL    | NULL      | 1000610 | Using where     |
| NULL | UNION RESULT | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL      |    NULL | Using filesort  |
+----+--------------+------------+--------+---------------+---------+---------+-----------+---------+-----------------+
...
Рейтинг: 0 / 0
27.04.2014, 12:52:04
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38626958
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
Все созданные индексы заносил в "возможные"... Видать из-за некорректности запроса.
...
Рейтинг: 0 / 0
27.04.2014, 12:54:10
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627040
chabapok
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chabapok
Участник
покажите крейт табл ваших таблиц
а то без индексов ничего сказать нельзя.
...
Рейтинг: 0 / 0
27.04.2014, 16:18:51
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627072
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
chabapokпокажите крейт табл ваших таблиц
а то без индексов ничего сказать нельзя.
Я их удалил уже, они были для creation_date_question и creation_date_answer - он их в возможные заносил. А это тут основное, ведь из-за этого перебор всех массивов идет.

Нужно сам sql запрос упростить.
...
Рейтинг: 0 / 0
27.04.2014, 17:32:30
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627090
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Навскидку - почитайте про селективность индекса и покрывающий индекс

Дополнительно: если индекс построен по одному полю, то для поиска данных по нему сервер вынужден выполнить 2 операции: быстрый поиск в этом индексе требуемого значения и определение для него значения первичного ключа, а затем поиск по кластерному индексу значения первичного ключа и получение значений остальных полей таблицы. Очень вероятна ситуация (зависит от селективности индекса), когда сервер предпочтет этой схеме простой перебор строк по кластерному индексу.
...
Рейтинг: 0 / 0
27.04.2014, 19:07:56
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627110
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
Cygapb-007Навскидку - почитайте про селективность индекса и покрывающий индекс

Дополнительно: если индекс построен по одному полю, то для поиска данных по нему сервер вынужден выполнить 2 операции: быстрый поиск в этом индексе требуемого значения и определение для него значения первичного ключа, а затем поиск по кластерному индексу значения первичного ключа и получение значений остальных полей таблицы. Очень вероятна ситуация (зависит от селективности индекса), когда сервер предпочтет этой схеме простой перебор строк по кластерному индексу.

Да, селективность большая. Создал покрывающие индексы и он стал их использовать. НО на результативность запроса не повлияло, стало работать быстрее на доли секунды лишь. Видать из-за накладных расходов.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+----+--------------+------------+--------+---------------+---------+---------+-----------+--------+--------------------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref       | rows   | Extra                    |
+----+--------------+------------+--------+---------------+---------+---------+-----------+--------+--------------------------+
|  1 | PRIMARY      | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL      |     10 | Using temporary          |
|  1 | PRIMARY      | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.id_user |      1 |                          |
|  2 | DERIVED      | question   | range  | q_cov         | q_cov   | 8       | NULL      |  50095 | Using where; Using index |
|  3 | UNION        | answer     | range  | a_cov         | a_cov   | 8       | NULL      | 500305 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL      |   NULL | Using filesort           |
+----+--------------+------------+--------+---------------+---------+---------+-----------+--------+--------------------------+
...
Рейтинг: 0 / 0
27.04.2014, 19:48:39
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627112
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
Индексы такие:
Код: sql
1.
2.
CREATE INDEX q_cov ON question(creation_date_question, id_user, question_rating);
CREATE INDEX a_cov ON answer(creation_date_answer, id_user, answer_rating);
...
Рейтинг: 0 / 0
27.04.2014, 19:49:52
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627128
chabapok
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chabapok
Участник
ну вот и ответ вам

условие ON (a.id_user = b.id_user); выполняется без использования индексов
...
Рейтинг: 0 / 0
27.04.2014, 20:17:08
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627131
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
chabapokну вот и ответ вам

условие ON (a.id_user = b.id_user); выполняется без использования индексов

Это условие для таблицы <derived2> состоящей всегда из 10 элементов, смысла добавлять индекс нету.
...
Рейтинг: 0 / 0
27.04.2014, 20:21:19
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627180
chabapok
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chabapok
Участник
derived2 с 10 записями это у вас таблица а. А для таблицы b это условие выполняется без индексов. Сколько записей у вас в b?

Вообще странно, у вас в эксплейне для b используется primary-ключ, а про индекс для user вы ничего не написали.
Вобщем, мы возвращаемся к началу - надо крейт таблы, а не сказки про "я их удалил". т.к. без них нет полной картины и приходится проявлять телепатические способности чтобы понять что у вас там. Если вы удалили эти таблицы, то зачем вам оптимизировать запрос по ним? А если таблицы есть, что что мешает сделать show ctreate table?

А еще тормозит у вас этап filesort, и по всей видимости - сильно. То что там limit 10 не должно вводить в заблуждение, т.к. всех пользователей оно сортирует, и только потом выбирает 10 первых.

Я б шел по такому пути - выбирается 10 лидеров из одной таблицы, потом 10 из другой, потом юнион - и получаем 20 лидеров. А отсортировать 20 лидеров - это вам не 1000610.

Но проблема в том, что запрос select * from x WHERE a>b ORDER BY d - нехорош тем, что индекс может использоваться либо для WHERE, либо для ORDER BY но не одновременно. Так что тут скорей всего, придется менять структуру базы и добавлять какие-то оптимизации этого момента.
...
Рейтинг: 0 / 0
27.04.2014, 22:16:55
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627184
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторпрос select * from x WHERE a>b ORDER BY d - нехорош тем, что индекс может использоваться либо для WHERE, либо для ORDER BY но не одновременно.

бред.
...
Рейтинг: 0 / 0
27.04.2014, 22:23:47
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627186
chabapok
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chabapok
Участник
вобщем, навскидку, я бы наверное вел отдельную таблицу, из которой бы периодически чистил бы строки со старыми creation_date
Тогда бы никакого WHERE не нужно было бы, т.к. в таблице гарантируются только новые записи.

Но такое прокатывает, только если у вас диапазон времени всегда одинаков, скажем всегда последние Х дней. А если у вас кажждый раз это Х разное, то навскидку не скажешь, надо думать :)
...
Рейтинг: 0 / 0
27.04.2014, 22:27:23
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627188
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
фраза "DERIVED" никому ничего не говорит?
...
Рейтинг: 0 / 0
27.04.2014, 22:29:55
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627189
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
ScareCrowавторпрос select * from x WHERE a>b ORDER BY d - нехорош тем, что индекс может использоваться либо для WHERE, либо для ORDER BY но не одновременно.бред.Не совсем бред. Для конкретного случая, когда поиск происходит по диапазону, увы, это реальность.
Хотя в общем, конечно, так утверждать нельзя.
...
Рейтинг: 0 / 0
27.04.2014, 22:30:46
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627190
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
автор. Для конкретного случая, когда поиск происходит по диапазону, увы, это реальность.
а пример дайте?
...
Рейтинг: 0 / 0
27.04.2014, 22:33:37
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627192
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
webslonНужно вывести 10 пользователей у которых были самые популярные вопросы или ответы начиная с определенного момента времени.Вы таки уточните задачу. А то предложенный запрос ее не решает. По крайней мере, он вполне может выдать и меньше 10 пользователей.
...
Рейтинг: 0 / 0
27.04.2014, 22:34:18
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627193
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
ScareCrowавтор. Для конкретного случая, когда поиск происходит по диапазону, увы, это реальность.
а пример дайте?Пример чего именно? неиспользования индекса для обоих операций одновременно?
...
Рейтинг: 0 / 0
27.04.2014, 22:35:34
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627194
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
ну хоть чего нибудь дайте.
...
Рейтинг: 0 / 0
27.04.2014, 22:35:57
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627195
chabapok
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chabapok
Участник
ScareCrowавторпрос select * from x WHERE a>b ORDER BY d - нехорош тем, что индекс может использоваться либо для WHERE, либо для ORDER BY но не одновременно.

бред.

http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
...
You use ORDER BY on nonconsecutive parts of a key:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
...
The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
...
Рейтинг: 0 / 0
27.04.2014, 22:36:11
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627196
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
моя версия запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT b.login 
FROM
( 
 (SELECT id_user, question_rating AS rating
 FROM question 
 WHERE creation_date_question >= '2013-04-15 00:00:00'
 ORDER BY rating DESC 
 LIMIT 10)
 UNION
 (SELECT id_user, answer_rating AS rating
 FROM answer 
 WHERE creation_date_answer >= '2013-04-15 00:00:00'
 ORDER BY rating DESC 
 LIMIT 10)
 ORDER BY rating DESC 
 LIMIT 10
) AS a
JOIN user AS b ON a.id_user = b.id_user

Эта версия имеет тот же недостаток - может выдать меньше 10 пользователей.
...
Рейтинг: 0 / 0
27.04.2014, 22:41:51
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627199
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
ScareCrowну хоть чего нибудь дайте.Могу дать уточнение - обычный B-Tree индекс не может одновременно использоваться для отбора записей по диапазону и сортировки по другому полю, однако может использоваться как источник данных, т.е. как покрывающий индекс.
...
Рейтинг: 0 / 0
27.04.2014, 22:48:43
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627216
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
miksoftмоя версия запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT b.login 
FROM
( 
 (SELECT id_user, question_rating AS rating
 FROM question 
 WHERE creation_date_question >= '2013-04-15 00:00:00'
 ORDER BY rating DESC 
 LIMIT 10)
 UNION
 (SELECT id_user, answer_rating AS rating
 FROM answer 
 WHERE creation_date_answer >= '2013-04-15 00:00:00'
 ORDER BY rating DESC 
 LIMIT 10)
 ORDER BY rating DESC 
 LIMIT 10
) AS a
JOIN user AS b ON a.id_user = b.id_user

Эта версия имеет тот же недостаток - может выдать меньше 10 пользователей.
В том и проблема. Такой вариант сам рассматривал. Но нужно именно 10 всегда выводить.
...
Рейтинг: 0 / 0
27.04.2014, 23:24:04
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627222
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
chabapokderived2 с 10 записями это у вас таблица а. А для таблицы b это условие выполняется без индексов. Сколько записей у вас в b?

Вобщем, мы возвращаемся к началу - надо крейт таблы, а не сказки про "я их удалил". т.к. без них нет полной картины и приходится проявлять телепатические способности чтобы понять что у вас там. Если вы удалили эти таблицы, то зачем вам оптимизировать запрос по ним? А если таблицы есть, что что мешает сделать show ctreate table?



Я удалил не таблицы, а индексы все для этих таблиц. Сейчас только 2 индекса покрывающих есть a_cov и q_cov. Смысла в крейт таблы ну никак не вижу. Позволит вам разве что другие атрибуты посмотреть.
...
Рейтинг: 0 / 0
27.04.2014, 23:29:33
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627225
webslon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
webslon
Гость
miksoftwebslonНужно вывести 10 пользователей у которых были самые популярные вопросы или ответы начиная с определенного момента времени.Вы таки уточните задачу. А то предложенный запрос ее не решает. По крайней мере, он вполне может выдать и меньше 10 пользователей.

Мой запрос её не решает? Вроде решает всегда, кроме случаев когда для указанной даты не будет вопросов и ответов, но этим можно пренебречь.
...
Рейтинг: 0 / 0
27.04.2014, 23:32:50
| Ответить | Цитировать | Написать  
Помогите оптимизировать запрос пожалуйста
    #38627227
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
webslonНо нужно именно 10 всегда выводить.Тогда уточняйте задачу. Если какие-то из пользователей имеют несколько высокопопулярных запросов и несколько же ответов, то их как считать? за одного или за несколько?
...
Рейтинг: 0 / 0
27.04.2014, 23:35:05
| Ответить | Цитировать | Написать  
25 сообщений из 39, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос пожалуйста
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=180&tid=1834919]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 14ms
get forum data:
 2ms
get page messages:
 66ms
get tp. blocked users:
 1ms
others: 213ms
total:  355ms
созд. / загр.: 355ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]