0

we have some tables with a lot of records (millions) and try to keep everything up to speed, but on the other hand we are setting our application up in new countries and keep some data per country. At first we made columns per country, but as the number of countries grow and having a limit of 64 indexes per table (and a limit on row-length) we cannot proceed this way.

We thought of having a foo_countries table with a country column. But we now filter and sort in both tables in 1 query. This doesn't seem like the right way either. I've been reading a lot across the internet and some people say to include the actual data of the parent table into the related table, (optionally with triggers) but this seems overly complex and very prone to inconsistencies between different countries.

We also thought to have a country column on the table foo itself, but having this duplicates all records per country and doesn't help in the relations to this table (there are FK's referencing foo).

Current table structure (simplified):

CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NULL DEFAULT NULL,
  `slug` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand_id` int(11) unsigned NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_count_fr` int(11) NOT NULL DEFAULT -1,
  `product_count_de` int(11) NOT NULL DEFAULT -1,
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo_slug_brand_id_unique` (`slug`,`brand_id`),
  KEY `foo_brand_product_count_fr_idx` (`brand_id`,`product_count_fr`),
  KEY `foo_brand_product_count_de_idx` (`brand_id`,`product_count_de`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The query:

SELECT * FROM `foo` WHERE `view_count_fr` != 0 AND `brand_id` = 'bar' ORDER BY `name` ASC LIMIT 250 OFFSET 0;

This query gives a filesort in explain, which is actually logical since the product_count is a range lookup. We use this count as '-1' equals 'unknown/not yet known', 0 means no products, >0 means product_count products. We want to filter out everything without products. To make this more efficient we planned on extra columns has_products_XX with triggers to update them automatically. This mitigates the range lookup. But having this inside foo doesn't seem very durable as the country-count grows with 3-4 a year. But having a foo_countries table makes the filtering happen on both sides which is very slow since there are currently 3M+ records in foo and there would be 3M+ x countries records in foo_countries.

Any ideas on how to handle these situations?

New contributor
rvdh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • 1
    1. What does Product_Count represent? Number of foo offered in each country? Inventory? Context is important. 2. Never model 1 to many relationships flat.
    – bbaird
    Nov 22 at 17:02

1 Answer 1

0

No. As a generalization: "Do not spread an array of things across columns".

Have another table, say product_counts that has a column for country (fr, de, etc). It will have more rows than the current 'foo'.

If there are no products in a certain country, the row can be missing. To get 0 instead of NULL, use LEFT JOIN and COALESCE(ct, 0).

ALTER TABLE (to add a column and index, etc) should be avoided.

If you want more discussion, please sketch out this redesign, but include some hints of what kind of data will be stored. And provide a sample SELECT.

This design keeps: '-1' equals 'unknown/not yet known', 0 means no products, but uses a missing row to mean 'unknown/not yet known' (instead of "-1").

Use INSERT ... ON DUPLICATE KEY UPDATE ... to upsert counts into product_counts.

Your Answer

rvdh is a new contributor. Be nice, and check out our Code of Conduct.

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.