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
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 советуют.
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
В первую очередь убрать конфиг mysql (mariadb) совсем куда-нибудь, перезапустить службу /etc/mysql restart и смотреть.
При обычном конфиге даже 150 коннектов это держит легко.
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
Попробуй так.