Исходный запрос требующийся оптимизировать:

автор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 сек.
В таблице хранится: 6998873 строк
Тип таблицы: InnoDB

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

Поправка:

В таблице game_views хранится: 6998873 строк
В таблице game хранится: 80000 строк
Тип всех таблиц: InnoDB

miksoft, спасибо)

miksoft,

индексы уже созданы, забыл указать это в теме

miksoft,

ок

miksoft,

Разумеется, все поля id во всех 3-х таблицах -> primary key и autoincrement

miksoft,

сделал кто то до меня и до того кто был до меня)

javajdbc,

спасибо, сейчас попробую

miksoftjavajdbcу ТС -- неверный изначальный селект -- поля из gaim_view
не агрегированы.Ну значит ему все равно, что там выйдет.

я понял, что не верный поэтому помощи прошу, иначе бы у меня всё работало и к вам не обратился бы

javajdbcmiksoftпропущено...
Еще в типографии остальные поля из game_views пропустили.

Где Яростный Меч и ScareCrow когда в селекте есть поля
НЕ из Гроуп БУ ?
Почему я должен выполнять их работу?
:-)

у ТС -- неверный изначальный селект -- поля из gaim_view
не агрегированы.


Получаю ошибку в этой части запроса, не пойму как поправить:

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

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(game_views.id) AS gv_cnt
FROM `game_views` `game_views`
WHERE (`game_v' at line 3

javajdbcв типографии ошиблись,
следует читать:





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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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

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

<pre class="src lang-sql">
SELECT * FROM `game` `t` WHERE t.id <> 208405 ORDER BY rand() LIMIT 15;
</pre>

выполняется в среднем от 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 соответственно в данной таблице нет):


<pre class="src lang-sql">
select ifnull(sum(data_length + index_length), 0) from information_schema.tables where table_schema = 'dbgwg';
</pre>


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

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

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



1) SHOW CREATE TABLE game

<pre class="src lang-sql">
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
</pre>

2) SHOW CREATE TABLE tables

<pre class="src lang-sql">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</pre>

tanglirokuznetsovВ результате получились два запросамне кажется или это один и тот же запрос, только с изменённым порядком таблиц в джойне?
okuznetsov 1) Также хотелось бы узнать можно ли что-то сделать и с этим запросом: Скорее всего нет. Ордер бай ранд() работает дубово: выбирается всё, подходящее под условие, для каждой записи генерируется рандом, результат сортируется. Учитывая, что сортировать надо практически всю таблицу (если ид у вас - ПК), то сортировка запросто может задействовать диск. Превед производительность.

А запросы к инфо_схема и не обязаны быть быстрыми. Неужели вам требуется выполнять их N раз в секунду?

1) Именно так, один и тот же запрос, оформленный по разному. вроде бы по скорости одинаково выполняются. по стилю написания, т.е. хорошему тону какой соответствует?

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

Меня устраивает и следующий вариант реализации. Если вот таким образом сделать - это хорошее решение?

автор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;

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

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

А в чём плохая реализация заключается? По времени на моей таблице в 7 000 000 строк запрос стал выполняться достаточно быстро - в пределах от 0.040 - 0.080 сек, на порядок ускорился.

Вариант предложенный вами сейчас тоже изучу

javajdbcokuznetsovА в чём плохая реализация заключается? По времени на моей таблице в 7 000 000 строк запрос стал выполняться достаточно быстро - в пределах от 0.040 - 0.080 сек, на порядок ускорился.

Ваш вариант выбирате 15 ид подряд начиная
с некоторого случайного числа.
Если это и есть ваше определение 15 случайных ИД --
то ок.

Мой варинат -- 15 реально случайно разбросаных ид

Ваш вариант заинтересовал меня своим решением, и я попробовал потестировал его, в результате заметил одну не очень хорошую закономерность - случайные ИД генерируются в достаточно приближённом районе от максимального ID и ни как не из середины таблицы и тем более её начала. Возможно это какое-то странное совпадение, но я запускал запрос на выполнение подряд несколько сотен раз и интервалом в среднем 0.5 секунды.

В принципе меня устраивает и мой вариант, он хорош для меня как раз тем, что вытаскиваются подряд случайно рядом стоящие ИД, т.к. это есть одинаковые игры разных версий, а также похожие на рядом стоящие игры. Я так понял пока разбирался, что игры добавлялись в таблицу друг за дружкой сгруппированные по интересам и жанрам.

Если с моим запросом всё в порядке тогда беру его)

javajdbcokuznetsovА в чём плохая реализация заключается? По времени на моей таблице в 7 000 000 строк запрос стал выполняться достаточно быстро - в пределах от 0.040 - 0.080 сек, на порядок ускорился.

Ваш вариант выбирате 15 ид подряд начиная
с некоторого случайного числа.
Если это и есть ваше определение 15 случайных ИД --
то ок.

Мой варинат -- 15 реально случайно разбросаных ид



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

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

поправленный вами вариант, заработал, вижу стали генерироваться разные ИД, но скорость выполнения запроса упала в разы, на моей таблице составляет от 0.5 - 1 сек, это много. в том варианте который у javajdbc и менят- запрос выполняется от 0.040-0.100 сек.

спасибо за объяснение "Считает размер таблиц в схеме 'dbgwg'. По идее, чисто админская операция."

tanglirКстати, вы несколько раз проверяли? у меня стабильно за 0,05с отрабатывает, только первый запуск был 0,2с.

по отдельности быстро работают, каждый выполняется не более чем за 0.040 секунд.

а как вместе - я заснял для вас здесь -

okuznetsov,

лучше на ютьюбе посмотрите, перейдите по прямой ссылке _

okuznetsov,
курсор мышки нужно было просто убрать:) и не чего не отрезалось бы:) заработался)