MySQL, оптимизация запроса

Question

[fenric]

Вопрос закрыт, большое спасибо пользователю @edogs.

Answer 1

[Владимир Шишминцев]

через EXPLAIN смотрели?

Comments

[fenric]

hostingkartinok.com/show-image.php?id=41ee800e33f2...

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

Answer 2

[edogs]

1) Переверните джоин, джоиньте к B таблицу A, а не наоборот. В среднем лучше джоинить меньшее к большему, к тому же это позволит без напряга фильтровать по b.published.
2) Храните количество картинок отдельным полем в games, ну и a.small заодно если он так уж нужен (хотя его можно и выбирать в принципе, если правильно это сделать). Это позволит на таких запросах вообще обходится без джоина. Это же стандарт, количество постов хранить в таблице с темами, количество тем в таблице с форумами, соответственно количество картинок в таблице с играми...

Comments

[fenric]

1. join перевернуть по таблицам не выйдет, т.к. не у всех игр есть картинки.
2. Кол-во игр хранить в поле, идея неплохая, но накладная, в модели, во многих местах, придётся делать перерасчеты, и прочие действия...
3. Поле small в картинках, не может быть у игры в поле, у игры есть отдельное поле, в котором находится box art...

Ещё раз повторюсь, что логика запроса совершенно правильная.

[edogs]

Ещё раз повторюсь, что логика запроса совершенно правильная.
---
Она может и правильная, но не единственно верная и самое главное в контексте Вашего вопроса - не самая быстрая.
1. У Вас картинок меньше чем игр, несложно добавить "фейковые" игры.
2. Совсем не обязательно. Кол-во картинок изменяется только при изменении кол-ва картинок. Поставить триггер на пересчет кол--ва картинок и всего делов.
3. В Вашем случае логически поле small именно у игры, точнее представитель этого поля, ведь Вы делаете group by по game. Значит small по факту получается относящимся именно к game.

[fenric]

@edogs Вы не правильно прочитали мой вопрос, в том то и дело, что картинок в 10 раз больше чем игр.

2. В таблице #__games_images находится ~ 100 000 записей...
3. В таблице #__games находится примерно ~ 20 000 записей...

Ну ладно, не в 10 а в 5...

По третьему пункту мы не поймёт друг друга... Я храню 2 имени изображения, оригинальное и сжатое, они одинаковые, только размеры разные, small это копия оригинала размером 300х300, далее при вызове на сервере я его сжимаю на лету, допустим small.100x75.jpeg но это другая история, а по делу, у игры и так есть своя картинка записанная в поле у таблицы с играми, но там не скриншот а ART BOX...

[fenric]

@edogs А хранить small в поле с игрой, как минимум не правильно, в любой момент картинка может удалиться...

[edogs]

@fenric при удалении картинок можно естественным образом апдейтить поля с игрой, в этом и смысл. Редкий апдейт лучше постоянных сложных выборок.
А можете объяснить почему у Вас картинок больше чем игр и картинки в принципе могут быть без игр? Какова идея самого проекта?

[fenric]

@edogs  

[fenric]

@edogs рано радовался я, но всё таки решил, готовый оптимизированный запрос в шапке, обновил... :) Но всё равно спасибо)

Answer 3

[Виталий Архипов]

Мне кажется вам стоит считать количество картинок заранее: по триггеру или крону и все у вас будет!
(ну раз вам так важна скорость).

В любом случае, попробуйте заменить group by на вложенный подзапрос по __games_images, так хотя бы группироваться будет 1 таблица.
С индексами все просто:
Для каждого запроса нужно создать комбинированный индекс в таком порядке: сначала условия (ON и WHERE), затем группировка (если есть), затем сортировка.

Comments

[fenric]

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

[fenric]

@arvitaly И да, запрос в запросе, как альтернатива group by... По моему не самое удачное решение, если брать в расчёт тот факт, что кол-во записей в таблицах измеряется тысячами...

[Виталий Архипов]

@fenric по поводу подзапроса это вы как оценили удачность решения?)

[fenric]

@arvitaly Путём экспериментов, как же ещё...

[Виталий Архипов]

@fenric можно запросы и результаты увидеть?

Answer 4

[Сергей Протько]

что делает запрос? Там как я понимаю сейчас логика такая, что берутся 100К строк, это все дело идет домножается на 20К (ибо LEFT JOIN), затем вы группируете по id картинок (подозреваю что так вы проверяете случаи когда у игрушки нету картинки, ибо в любом случае результат счетчика будет либо 0 либо 1, если только id не уникален)... Логика довольно странная. Вам не кажется что можно намного проще?

Что касается условия `b`.`published` = 1, его можно писать в ON у JOIN, тогда проверка будет работать на меньших выборках и вообще вы будете иметь с меньшим количеством рядов. Но мне что-то подсказывает что у вас логика некорректная.

Comments

[Сергей Протько]

И вообще в вашем случае логичнее заменить секцию с left join * on на такую:

INNER JOIN `#__games` AS `b` ON `a`.`game` = `b`.`id` AND 'b'.`published` = 1

+ добавить индекс на поле create и published.

[fenric]

Логика запроса другая:
У игры есть изображения, я обращаюсь именно к изображениям, они или есть, или их нету.
Группирую я их, для получения кол-ва изображений у игры...
Сортирую я их по дате добавления, она же является индексом...

Т.е. с логикой то всё в порядке, а вот со скоростью нет...

[fenric]

@Fesor И запрос выполняется в 5 раз раз дольше, причём тут внутреннее объединение? Если тут правильнее левостороннее объединение...

[Сергей Протько]

в 5 раз дольше при каких изменениях? При внесении условия published=1 в on? при замене left join на inner join (у вас же картинки принадлежат играм, или картинка может быть сама по себе?) И все же зачем вам count и group by в запросе? там же связь один ко многим (игра к картинке)...

[Сергей Протько]

Индексы добавляли? Не обязательно даже составные индексы, в теории mysql если не сильно старая сама мердж индексов сделает. И после каждого изменения смотрите эксплейн.

[fenric]

@Fesor если left join заменить на inner join скорость выполнения запроса увеличивается в 5 раз.
Индексы все проставлены, комбинированные индексы не помогают, в вопросе я обновил информацию, по поводу группировки...
group by для поля a.game, мне необходимо для того, чтобы получить 1 последнюю картинку к игре, иначе я получу 5 картинок к одной и той же игре.
count() мне необходимо для того, чтобы узнать вообще кол-во картинок к этой игре (что в свою очередь на скорость запроса не влияет вообще).

[edogs]

group by для поля a.game, мне необходимо для того, чтобы получить 1 последнюю картинку к игре
-------
Офигенское, хотя и популярное, заблуждение. Порядок строк в group by не регламентирован, Вы получаете не последнюю, а случайную картинку. Хотя в большинстве случаев она случайно окажется или последней или первой, тем не менее полагаться на это нельзя.

[fenric]

@edogs Ну а как же order by, по вашему group by его проигнорирует?

[edogs]

@fenric сначала работает group by. Он создает представителя для каждой группы. order by работает уже с представителями группы, т.е. после group by. Читайте маны:)

[fenric]

@edogs Ну так мне это и нужно, в этом плане, всё рабтает верно, я же не написал что я полный дуб в бд...
Сейчас вообще запрос выглядит так:
$qb->select('count(a.id)')->alias('count')
->select('a.small', 'b.id', 'b.url', 'b.name')
->from('#__games_images')->alias('a')
->leftJoin('#__games')->alias('b')
->on('a.game', '=', 'b.id')
->on('b.published', '=', '1')
->order('a.create')->desc()
->group('a.game')->desc()
->limit(5);
И сортировка работает так, как мне и нужно... И от неё ни чего не зависит, в плане скорости...

[edogs]

@fenric Если у Вас несколько картинок к одной игре, то при группировке по a.game Вы получаете случайную картинку к игре. order by применяется к результатам ВСЕГО запроса, а не к результатам внутри группы.

[fenric]

@edogs Пожалуй вы правы, в таком случаи вообще всё усложняется....

[fenric]

@edogs И почему вам тут нельзя +1 поставить...

[fenric]

@edogs В другом вашем ответе поставил...

[fenric]

@edogs А как, в таком случаи, мне произвести сортировку внутри группы?

[fenric]

@edogs Еее!!! Да чувак, да! Спасибо, я вообще убрал сортировку, запрос выполняется за 0.00000, в конце концов, кто там разглядит на 100х60 пикселях новое это изображение, или нет.
Вопрос закрыт, тебе за проявленный интерес, и за то, что подталкнул на мысль, огромное спасибо!
Вообще странно, что сортировка по индексу, производила такие тормоза...

[Виталий Архипов]

Собственно поэтому я и писал про подзапрос)

[Сергей Протько]

сортировка ж у вас не по индексу шла, у вас в explain написано filesort, так что быстро это не может происходить.

Answer 5

[afiskon]

Попробуйте сделать составной b-tree индекс по a.id и a.create

Comments

[fenric]

Комбинированные индексы не помогают, забыл сказать ещё, что тип таблиц InnoDB...