2

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.

  • Kindly post a SHOW CREATE TABLE for your table. – Vérace Feb 7 '15 at 0:43
  • Instead of using num_rows, do SELECT COUNT(*) .. WHERE ...; -- it will be faster. – Rick James Feb 7 '15 at 6:33
  • Don't use the PHP mysql_* API; it is deprecated. Use the mysqli_* API. – Rick James Feb 7 '15 at 6:34
  • Never use text fields like VARCHAR() as primary key. Add an extra column INT(10) with UNSIGNED NOT NULL AUTOINCREMENT . – Kondybas Jul 25 '17 at 16:52
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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