SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 03.10.2014 16:15:56

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Требуется помощь в оптимизации запроса

SELECT
t.id AS t0_c0,
t.catid AS t0_c1,
t.title AS t0_c2,
t.object AS t0_c3,
t.description AS t0_c4,
t.image AS t0_c5,
t.votes_summ AS t0_c6,
t.votes_count AS t0_c7,
t.views AS t0_c8,
t.favorites AS t0_c9,
t.date AS t0_c10,
t.is_uniq AS t0_c11,
count(game_views.id) AS gv_cnt,
category.id AS t1_c0,
category.slug AS t1_c1,
category.title AS t1_c2,
category.description AS t1_c3,
category.meta_title AS t1_c4,
category.parent AS t1_c5,
category.total_views AS t1_c6,
game_views.id AS t4_c0,
game_views.game_id AS t4_c1,
game_views.date AS t4_c2,
game_views.user_id AS t4_c3
FROM `game` `t` LEFT OUTER JOIN `category` `category` ON (`t`.`catid`=`category`.`id`) INNER JOIN `game_views` `game_views` ON (`game_views`.`game_id`=`t`.`id`)
WHERE (`game_views`.`date` > DATE(NOW() - INTERVAL 7 DAY))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28 OFFSET 224




Скорость выполнения запроса (в среднем): 5 сек.
В таблице game_views хранится: 6998873 строк
В таблице game хранится: 80000 строк
Тип всех таблиц: InnoDB



Скриншоты EXPLAIN EXTENDED SELECT.... и SHOW WARNINGS:


Забыл сообщить, что индексы уже создал и запрос ускорился, но не достаточно. Скриншот тоже прикрепляю:

Отредактированно okuznetsov (03.10.2014 16:41:45)


Прикрепленные файлы:
Attachment Icon mysql.jpg, Размер: 243,958 байт, Скачано: 617

Неактивен

 

#2 03.10.2014 18:46:03

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Требуется помощь в оптимизации запроса

1. У вас некорректный запрос, посмотрите http://sqlinfo.ru/articles/info/18.html

2. Группировку лучше поместить во from подзапрос

3. Нужен ли именно left join? (т.е. возможна игра без категории).

Неактивен

 

#3 04.10.2014 22:28:46

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

vasya написал:

1. У вас некорректный запрос, посмотрите http://sqlinfo.ru/articles/info/18.html

2. Группировку лучше поместить во from подзапрос

3. Нужен ли именно left join? (т.е. возможна игра без категории).

1. и 2. Предполагал, что запрос не верный. не особо силён в mysql, чтобы справиться с поправкой запроса самостоятельно. сейчас буду изучать вашу ссылку.

3. вы верно заметили про left join. я не указал в предыдущем сообщении, забыл, да действительно можно обойтись без категории, она не обязательна.

Неактивен

 

#4 04.10.2014 23:26:20

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

В общем, в результате у меня получились два запроса, вроде бы оба одинаково правильных. Хотелось бы чтобы вы порекомендовали какой из них более правильный (может быть с точки зрения качественного стиля написания или рассчитанный на скоростное выполнение при росте в будущем нагрузки)? А также, вообще это правильные запросы, т.е. правильно ли написаны с точки зрения функционирования?


SELECT *
FROM
(
SELECT
game_views.game_id,
count(game_views.id) AS gv_cnt
FROM  `game_views` `game_views`
WHERE (`game_views`.`date` > DATE(NOW() - INTERVAL 7 DAY))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28 OFFSET 224
) t1
JOIN game ON (game.id=`t1`.`game_id`)




или


SELECT *
FROM `game` `game` JOIN
(
SELECT
game_views.game_id,
count(game_views.id) AS gv_cnt
FROM `game_views` `game_views`
WHERE (`game_views`.`date` > DATE(NOW() - INTERVAL 7 DAY))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28 OFFSET 224
) as t1 ON (`game`.`id`=`t1`.`game_id`)

Неактивен

 

#5 04.10.2014 23:32:52

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Требуется помощь в оптимизации запроса

От перестановки мест слагаемых сумма не изменится smile

Только вместо * укажите в явном виде нужные вам поля.

Неактивен

 

#6 05.10.2014 01:06:49

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

ясно. спасибо за помощь)

Неактивен

 

#7 05.10.2014 02:36:51

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

1) Также хотелось бы узнать можно ли что-то сделать и с этим запросом:


SELECT * FROM `game` `t` WHERE t.id <> 208405 ORDER BY rand() LIMIT 15;


выполняется в среднем от 0.5 - 1 сек, но бывают моменты когда более 3 секунд

EXPLAIN EXTENDED:

1    SIMPLE    t    range    PRIMARY    PRIMARY    4        49391    100,00    Using where; Using temporary; Using filesort


2) И хотелось бы что-то сделать с запросом такого плана (primary key соответственно в данной таблице нет):


select ifnull(sum(data_length + index_length), 0) from information_schema.tables where table_schema = 'dbgwg';
 


выполняется в среднем около 0.5 сек в среднем бывают моменты когда более 1 секунды

EXPLAIN EXTENDED следующий:

1    SIMPLE    tables    ALL        TABLE_SCHEMA                    Using where; Open_full_table; Scanned 1 database

Неактивен

 

#8 05.10.2014 02:41:40

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

1) SHOW CREATE TABLE game

CREATE TABLE `game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `object` text CHARACTER SET utf8 NOT NULL,
  `description` text CHARACTER SET utf8 NOT NULL,
  `image` varchar(255) NOT NULL,
  `votes_summ` int(11) NOT NULL,
  `votes_count` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  `favorites` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `is_uniq` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`),
  KEY `date` (`date`),
  KEY `favorites` (`favorites`),
  KEY `views` (`views`),
  KEY `title` (`title`),
  KEY `votes_summ` (`votes_summ`,`votes_count`),
  KEY `is_uniq` (`is_uniq`)
) ENGINE=InnoDB AUTO_INCREMENT=268052 DEFAULT CHARSET=latin1


2) SHOW CREATE TABLE tables

CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8

Неактивен

 

#9 05.10.2014 12:10:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Требуется помощь в оптимизации запроса

По первому вопросу посмотрите FAQ №9

Неактивен

 

#10 05.10.2014 22:06:42

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

Если вот таким образом сделать - это хорошее решение?

SELECT *
FROM game AS r1
JOIN
(
SELECT (RAND() * (SELECT MAX(id) FROM game)) AS id
) AS r2
WHERE r1.id >= r2.id
#ORDER BY r1.id ASC
LIMIT 15;

Неактивен

 

#11 05.10.2014 22:08:44

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

Расшифровка (на всякий случай):

JOIN добавляет все ID который больше или равны нашему случайному значению и мы выбираем ближайшего соседа, если равенство не возможно. НО как только 15 строк найдено мы останавливаемся.

Неактивен

 

#12 05.10.2014 23:35:46

okuznetsov
Участник
Зарегистрирован: 03.10.2014
Сообщений: 13

Re: Требуется помощь в оптимизации запроса

А по второму запросу можете помочь? Я не знаю и не понимаю пока для чего и зачем он нужен, но он находится в моём списке медленных запросов mysql-slow.log и мне нужно с ним разобраться

Неактивен

 

#13 06.10.2014 13:49:37

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Требуется помощь в оптимизации запроса

okuznetsov написал:

Если вот таким образом сделать - это хорошее решение?

SELECT *
FROM game AS r1
JOIN
(
SELECT (RAND() * (SELECT MAX(id) FROM game)) AS id
) AS r2
WHERE r1.id >= r2.id
#ORDER BY r1.id ASC
LIMIT 15;

Плохое.
1. Выбираются не 15 случайных, а 15 подряд от случайного места.
2. Возможен вариант, когда запрос вернет меньше 15 значений, если случайная позиция будет выбрана близко к максимальному id.

Неактивен

 

#14 06.10.2014 13:54:48

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Требуется помощь в оптимизации запроса

okuznetsov написал:

А по второму запросу можете помочь? Я не знаю и не понимаю пока для чего и зачем он нужен, но он находится в моём списке медленных запросов mysql-slow.log и мне нужно с ним разобраться

Важен не факт медленного выполнения запроса, а его частота использования.
Напрямую вы его не улучшите. Если же он выполняется часто, то нужно будет смотреть приложение где он используется - насколько точные данные там нужны, возможно ли кеширование.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson