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

okuznetsov · 03.10.2014 16:15:56

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)

Прикрепленные файлы:

mysql.jpg, Размер: 243,958 байт, Скачано: 645

vasya · 03.10.2014 18:46:03

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

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

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

okuznetsov · 04.10.2014 22:28:46

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

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

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

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

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

okuznetsov · 04.10.2014 23:26:20

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

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`)

vasya · 04.10.2014 23:32:52

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

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

okuznetsov · 05.10.2014 01:06:49

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

okuznetsov · 05.10.2014 02:36:51

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

okuznetsov · 05.10.2014 02:41:40

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

vasya · 05.10.2014 12:10:02

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

okuznetsov · 05.10.2014 22:06:42

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

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;

okuznetsov · 05.10.2014 22:08:44

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

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

okuznetsov · 05.10.2014 23:35:46

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

vasya · 06.10.2014 13:49:37

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.

vasya · 06.10.2014 13:54:48

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

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

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

#1 03.10.2014 16:15:56

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

#2 03.10.2014 18:46:03

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

#3 04.10.2014 22:28:46

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

vasya написал:

#4 04.10.2014 23:26:20

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

#5 04.10.2014 23:32:52

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

#6 05.10.2014 01:06:49

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

#7 05.10.2014 02:36:51

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

#8 05.10.2014 02:41:40

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

#9 05.10.2014 12:10:02

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

#10 05.10.2014 22:06:42

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

#11 05.10.2014 22:08:44

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

#12 05.10.2014 23:35:46

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

#13 06.10.2014 13:49:37

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

okuznetsov написал:

#14 06.10.2014 13:54:48

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

okuznetsov написал:

Board footer