Как оптимизировать SQL запрос с order by?

Question

[grisha2217]

Всем привет. У меня есть довольно сложный запрос для поиска элементов по базе данных с несколькими inner join. В конце запроса я пишу ORDER BY item.price DESC LIMIT 20, он выполняется за 0.3 секунды, меня устраивает такой показатель. А если поменять сортировку с DESC на ASC, то выполняется уже за 5.9 секунд.
Индекс на price стоит.
Как быть?

Сам запрос, если нужно:

запрос

SELECT item.*, 
       user.username, 
       user.avatar_date, 
       user.is_banned, 
       user.user_group_id, 
       user.secondary_group_ids, 
       user.display_style_group_id, 
       market_user.*, 
       steam_item.* 
FROM   market_item AS item 
       LEFT JOIN xf_user AS user 
              ON ( item.user_id = user.user_id ) 
       INNER JOIN market_user 
               ON ( item.user_id = market_user.user_id ) 
       LEFT JOIN market_steam_item AS steam_item 
              ON ( item.item_id = steam_item.steam_item_id ) 
WHERE  ( item.category_id = 1 ) 
       AND ( item.item_state = 'active' ) 
       AND ( (SELECT Count(*) 
              FROM   market_item_game 
              WHERE  market_item_game.item_id = item.item_id 
                     AND (( game_id = 730 
                            AND market_item_game.is_banned = 0 ))) = 1 ) 
       AND ( steam_item.steam_csgo_ban_date < 1579026194 ) 
       AND ( steam_item.account_community_ban = 0 ) 
ORDER  BY item.price DESC 
LIMIT  20

Comments

[Алексей Уколов]

Покажите explain обоих вариантов.

[grisha2217]

Алексей Уколов,
DESC:

ASC:

Answer 1

[Лев Забудкин]

SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
LIMIT 20
)
ORDER BY item.price ASC

Comments

[grisha2217]

Поправьте, пожалуйста.
#1248 - Every derived table must have its own alias

Не знаю, где и как перемеиновать таблицы

[Лев Забудкин]

SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
LIMIT 20
)
AS T ORDER BY item.price ASC

[grisha2217]

Лев Забудкин, я так пробовал))
Ошибка: #1060 - Дублирующееся имя столбца 'user_id'

[Лев Забудкин]

SELECT *
FROM (
SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
LIMIT 20
)
AS T ORDER BY item.price ASC

Ну здесь же школа, ну учиться надо :) Сам же видишь, поля разные.
Думал ты поймёшь, что таблицу надо делать временную, тыне понял, я показал.
Ты столкнулся с именами столбцов, вот тогда так.
Теперь точно выберется.
Сделай результат запроса через print_r
и посмотри что получилось.
Отсортируется махом. Это так на stackoverflow советуют.

[grisha2217]

Лев Забудкин, окей, у таблицы item и market_user есть одинаковые user_id. У меня никогда конфликтов таких не было, но ладно.
Убираю market_user из select - работает. Пробую переименовать столбец в select - не работает:

market_user.*, market_user.user_id AS market_user_user_id

Мне, что, перечислять в SELECT все столбцы из таблицы market_user?
market_user.balance, market_user.hold, ...

[Лев Забудкин]

SELECT *
FROM (
SELECT item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
LIMIT 20 AS tableIN
)
AS TableOut ORDER BY item.price ASC

Попробуй так.

[Лев Забудкин]

Иначе довольствоваться 5 секундами :(
тут я бессилен.

[grisha2217]

Лев Забудкин, не работает. Пробовал расставлять скобочки по-разному (до FROM, до SELECT), не работает, ошибка синтаксиса. Ладно, буду столбцы переписывать вручную)) Хотя бы работает.

[Лев Забудкин]

grisha2217,

SELECT 
item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT  * 
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
LIMIT 20
)
AS TableOut ORDER BY item.price ASC

[Лев Забудкин]

grisha2217, сейчас попробуй

[grisha2217]

Лев Забудкин, неа.
Щас вглянулся внимательно на результаты и дико поржал. Ваш запрос возвращает далеко не самые дешевые товары. Он берет первые 20 подходящих строк, и дальше их сортирует по цене)) В итоге сортируются по цене первые 20, а не вся таблица.
В общем, не работает!

[Лев Забудкин]

grisha2217, правильно, Вы же поставили LIMIT 20

А так:

SELECT 
item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT  * 
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
)
AS TableOut ORDER BY item.price ASC

[Лев Забудкин]

grisha2217,
Если Вам надо всего 20 результатов из общей сортировки, то вот:

SELECT 
item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT  * 
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
)
AS TableOut ORDER BY item.price ASC 
LIMIT 20

[grisha2217]

Лев Забудкин, тогда он выполняется ровно столько же, сколько и мой исходный) Ваш изначальный запрос работал быстро, ибо он брал первые 20 подходящих строк, а не все.

[Лев Забудкин]

grisha2217, ясно, тогда уж результ (проанализируй все сообщения сверху вниз):

SELECT 
item.*,
user.username,
user.avatar_date,
user.is_banned,
user.user_group_id,
user.secondary_group_ids,
user.display_style_group_id,
market_user.*,
steam_item.*
FROM (
SELECT  * 
FROM market_item AS item
LEFT JOIN xf_user AS user
ON ( item.user_id = user.user_id )
INNER JOIN market_user
ON ( item.user_id = market_user.user_id )
LEFT JOIN market_steam_item AS steam_item
ON ( item.item_id = steam_item.steam_item_id )
WHERE ( item.category_id = 1 )
AND ( item.item_state = 'active' )
AND ( (SELECT Count(*)
FROM market_item_game
WHERE market_item_game.item_id = item.item_id
AND (( game_id = 730
AND market_item_game.is_banned = 0 ))) = 1 )
AND ( steam_item.steam_csgo_ban_date < 1579026194 )
AND ( steam_item.account_community_ban = 0 )
ORDER  BY item.price DESC 
LIMIT  20
)
AS TableOut ORDER BY item.price ASC

[grisha2217]

Лев Забудкин, тут берет 20 самых дорогих и сортирует их возрастанию.

[Лев Забудкин]

grisha2217, ну и? DESC и ASC поменяешь сам местами? ;) Поиграй с ними и реши как тебе надо.
Время сократилось так как тебе надо?

[grisha2217]

Лев Забудкин, да пробовал, 6 секунд...

[Лев Забудкин]

grisha2217,
посмотри индексы у все_такие_id
зайди в phpmyadmin и создай индексы им.

[Лев Забудкин]

grisha2217,

сделай индексы и ты увеличишь скорость в разы!

[grisha2217]

Лев Забудкин, у меня primary key стоит везде, а если user_id это не главный столбец, то у него есть индекс. Посмотри скрины в комментах к ответу, я там дал скрин с explain.

[grisha2217]

Лев Забудкин, https://i.imgur.com/70Q0PDr.png
https://i.imgur.com/8V47KGq.png

[Лев Забудкин]

grisha2217,

Покажи индексы.

Виду нет ключа для item_id например, а он в выборке участвует.

Индексы никогда не навредят, единственное - базу могут раздуть, но и то не существенно, если это не строки, а числа.

[grisha2217]

Лев Забудкин, у меня у item_id уже есть primary key, индекс для него не нужен.

https://i.imgur.com/rLgTOFo.png
https://i.imgur.com/VpYEhEX.png

[Лев Забудкин]

grisha2217, надо бы сюда спеца по запросам SQL.
Я тут уже бессилен. Как мог помог, я старался.
Я пригласил спеца, надеюсь поможет Вам.