Optimising SQL Query

Question

Currently I have a query to calculate the averages of auctions that are present within a game, in order to do this I have the following query:

SELECT item_name, tier
     , CAST(SUM(highest_bid) AS UNSIGNED) as totalBids
     , CAST(SUM(amount) AS UNSIGNED) as totalAmount
     , CAST(SUM(highest_bid)/SUM(amount) AS UNSIGNED) as totalAvg 
FROM `auctions` 
WHERE CURRENT_TIMESTAMP() >= end 
  AND highest_bid > N 
GROUP BY concat(item_name, 'S', tier) 
ORDER BY item_name ASC

The issue here is mainly performance, as this query takes a good 10-15s or higher. My other queries are fine as they've been indexed. I suggest its due to the CONCAT, is there any better way you'd all do this?

The end objective is to get the average price an auction sold for, ranked by tier.

E.g.

Sword-Common | $500

Hope this clarifies, thank you.

(From comments)

CREATE TABLE `auctions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(45) NOT NULL,
  `auctioneer_id` varchar(45) NOT NULL DEFAULT '',
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `item_name` varchar(45) NOT NULL,
  `item_lore` text NOT NULL,
  `extra` text NOT NULL,
  `category` varchar(45) NOT NULL DEFAULT '',
  `tier` varchar(45) NOT NULL,
  `starting_bid` int(11) NOT NULL,
  `item_bytes` text NOT NULL,
  `claimed` tinyint(1) NOT NULL,
  `highest_bid` int(11) NOT NULL,
  `amount` tinyint(4) DEFAULT '0',
  `anvil_uses` tinyint(4) DEFAULT '0',
  `bin` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`) USING BTREE,
  KEY `ending_index` (`end`) USING BTREE,
  KEY `name_index` (`item_name`) USING BTREE,
  KEY `hb_index` (`highest_bid`) USING BTREE,
  KEY `auctions_idx_bin_end` (`bin`,`end`),
  KEY `time_indx` (`start`,`end`) USING BTREE,
  KEY `category` (`category`) USING BTREE,
  KEY `common_indx` (`end`,`highest_bid`,`bin`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

and

1 SIMPLE auctions ALL ending_index,hb_index,common_indx NULL NULL NULL 11146612 Using where; Using temporary; Using filesort

Adding INDEX(end, highest_bid, item_name, tier, amount) led to

1 SIMPLE auctions range ending_index,hb_index,common_indx,cmb_indx cmb_indx 5 NULL 5579192 Using where; Using index; Using temporary; Using filesort

Answer 1

There is not a lot that can be done. But one thing:

There are probably 2 sorts. (See EXPLAIN FORMAT=JSON SELECT ... for details.) You can get rid of one of them by making the ORDER BY the same as the GROUP BY:

GROUP BY item_name, tier 
ORDER BY item_name, tier 

As a minor improvement, I got rid of the useless CONCAT. (In this formulation it does not matter whether item_name is a constant length.)

It will give you virtually the same ordering, but faster because the Optimizer can know to skip the ORDER BY.

There is some chance that this composite index will help:

INDEX(item_name, tier)

Another possibility is to build and maintain a "Summary table": http://mysql.rjweb.org/doc.php/summarytables