Mysql – Slow ranking values in MySQL

countMySQLperconaperformance

I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers.

I'm supposed to display well over 70 different rankings on every individual product's page.

Example: #23 best seller in China, #732 best seller in USA, #13 cheapest price in Norway, etc.

I've thought of various solutions to this problem:

  1. Create ranking fields – I guessed it wouldn't work since just a single drop in price would mean updating millions of rows to reduce their ranking field by 1 for example.
  2. Use COUNT – COUNT the amount of products cheaper than X : display ranking. I'm still considering this, however a single query takes 200 ms on average, so running 70 of them looks like it would take ages.

Really looking forward to advice from experienced database admins on this topic, as my knowledge feels very basic.

Additional information:

Hardware is dual Xeon CPUs, 15k RPM hard drives, 32 GB DDR3, LEMP stack, running latest Percona, all tables are XtraDB engine.

Edit: Posting CREATE statement as requested

CREATE TABLE `products` (
  `id` varchar(64) COLLATE utf8_bin NOT NULL,
  `retailer_id` int(10) unsigned NOT NULL,
  `sales_usa` int(10) unsigned NOT NULL,
  `sales_eu` int(10) unsigned NOT NULL,
  `sales_asia` int(10) unsigned NOT NULL,
  `sales_africa` int(10) unsigned NOT NULL,
  `sales_france` int(10) unsigned NOT NULL,
  `sales_germany` int(10) unsigned NOT NULL,
  `sales_russia` int(10) unsigned NOT NULL,
  `sales_china` int(10) unsigned NOT NULL,
  `sales_india` int(10) unsigned NOT NULL,
  `sales_uk` int(10) unsigned NOT NULL,
  `sales_spain` int(10) unsigned NOT NULL,
  `sales_norway` int(10) unsigned NOT NULL,
  `sales_sweden` int(10) unsigned NOT NULL,
  `sales_japan` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Sample ranking statement:

SELECT `id` FROM `products` WHERE `sales_japan` > 12345

After which I perform a mysql_num_rows() on the result to get that product's ranking in Japan for example.

Best Answer

Create an EVENT to periodically UPDATE a stats table that stores the "last_ID" to which each of the 70 rankings was last pulled, and the current counts for each.

Each UPDATE on the stats table entries (one per ranking), should join the real table with the entries you are counting, and have the respective criteria, setting stats.count to stats.count+real_tbl.COUNT(*), AND adding WHERE criteria to only consider WHERE id > stats.last_id. You will therefore also be setting stats.last_id to now = MAX(realtbl.id)

Each time the update runs, it will be limited to counting only entries added since the last run, and it will find them by the primary key index (ultra fast).

Related Question