We have a view table the query for the view is pasted below. The problem is that whenever we try to query items view our database start making sorting index which make the over all time of our response very slow.

CREATE VIEW items_view AS
SELECT item_detail.*, item.name, item.description, item.thumbnail_url, item.large_image_url, item.ios_url, item.android_url, item.vr_url, item.vr_updated_url, item.webgl_url, item.video_url, item.quest_url,
item.is_transferable, item.ip_id, item.brand_id, item.edition_id, item.currency_id, item.category_id, item.rarity_id, item.type_id, item.asset_id, item.artist_id, item.art_collection_id,
IFNULL(item.variation_id, 0) AS variation_id,
item.series_id, item.set_id, item.is_tradable, item.number_minted, ip.name AS ip_name, brand.name As brand_name, brand.value AS brand_image,
edition.name AS edition_name, type.name As type_name, artist.name AS artist_name, art_collection.name AS art_collection_name,
IFNULL(variation.name, '') As variation_name, variation.value AS variation_value, category.name As category_name, asset.name AS asset_name, asset.value AS asset_image,
rarity.name As rarity_name, currency.symbol, currency.code AS currency_code, currency.name AS currency_name, series.name AS series_name, sets.name AS set_name,
CONCAT(users.first_name, ' ' ,users.last_name) as owner_name, users.username AS owner_username, users.is_public_inventory, users.role, users.pub_key,
auctions.id as auction_id, auctions.expire_at as auction_expire_at, max(ab.bid_amount) AS highest_bid_amount, IF(COUNT(auctions.id) > 0 AND !auctions.is_completed AND !auctions.is_deleted
, true, false) AS is_auction, auctions.start_amount AS auction_start_amount, auctions.reserved_amount AS auction_reserved_amount, auctions.buy_now_amount AS auction_buy_now_amount,
auctions.created_at as auction_created_at, auctions.status AS auction_status, auctions.is_extended AS auction_extended, TIMESTAMPDIFF(SECOND, now(), auctions.expire_at) AS auction_expire_seconds FROM item
LEFT JOIN base_lookup AS ip ON item.ip_id = ip.id
LEFT JOIN base_lookup AS brand ON item.brand_id = brand.id
LEFT JOIN base_lookup AS edition ON item.edition_id = edition.id
LEFT JOIN base_lookup AS category ON item.category_id = category.id
LEFT JOIN base_lookup AS type ON item.type_id = type.id
LEFT JOIN base_lookup AS variation ON item.variation_id = variation.id
LEFT JOIN base_lookup AS rarity ON item.rarity_id = rarity.id
LEFT JOIN base_lookup AS series ON item.series_id = series.id
LEFT JOIN base_lookup AS sets ON item.set_id = sets.id
LEFT JOIN base_lookup AS asset ON item.asset_id = asset.id
LEFT JOIN base_lookup AS artist ON item.artist_id = artist.id
LEFT JOIN base_lookup AS art_collection ON item.art_collection_id = art_collection.id
INNER JOIN item_detail ON item.id = item_detail.parent_id
INNER JOIN users ON users.id = item_detail.owner_id
LEFT JOIN currency ON currency.id = item.currency_id
LEFT JOIN auctions on (item_detail.id = auctions.item_detail_id AND auctions.is_deleted = 0 AND auctions.is_completed = 0)
LEFT JOIN auction_bidding ab on auctions.id = ab.auction_id
WHERE item_detail.is_active = 1
group by item_detail.id
order by item_detail.id;

One of the query we are doing on this items_view that is stuck in making sorting index

  `id`, `code`, `name`, `large_image_url`, 
  `thumbnail_url`, `medium_thumbnail_url`, `symbol`, `ip_name`, `owner_id`, 
  `auction_id`, `is_auction`, `highest_bid_amount`, `auction_expire_at`, 
  `auction_start_amount`, `enable_sale`, `owner_name`, `role`, `amount`, 
  `auction_expire_seconds`, `rarity_name`, `ip_content`, `asset_content` 
FROM `items_view` AS `items_view` 
WHERE `items_view`.`transaction_hash` IS NOT NULL AND 
  `items_view`.`lobby_name` = 'live' AND 
  `items_view`.`is_tradable` = 1 AND 
  `items_view`.`is_transferring` = 0 AND 
  `items_view`.`is_locked` = 0 AND 
  `items_view`.`enable_sale` = true AND 
  `items_view`.`is_auction` = 0 
ORDER BY `items_view`.`item_order` ASC LIMIT 24, 24;

can any give any suggest what are we doing wrong here!

Edited: link to explain run against the above query link to csv

  • Where is item_order defined?
    – Rick James
    Commented Mar 1, 2021 at 21:40
  • @RickJames item_order is defined in item_detail table and in view we are getting it with item_detail.* Commented Mar 2, 2021 at 8:02

1 Answer 1

item_detail:  INDEX(is_active, owner_id, parent_id, id)

might help. It is "covering" and the columns are probably in the optimal order.

There are too many missing columns for me to analyze further. (item_order, transfer_hash, etc) Without knowing which table each thing is in, I can't write more INDEXes.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.