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:
- 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.
- 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.
VARCHAR()
as primary key. Add an extra columnINT(10)
withUNSIGNED NOT NULL AUTOINCREMENT
. – Kondybas Jul 25 '17 at 16:52