ALTER TABLE fm_variant_states
  ADD COLUMN product_variant_count INT UNSIGNED NOT NULL DEFAULT 1 AFTER is_parent_variant,
  ADD KEY fm_variant_states_client_variant_count_ix (client, product_variant_count);

UPDATE fm_variant_states v
INNER JOIN (
  SELECT client, product_id, COUNT(*) AS variant_count, MIN(variant_id) AS parent_variant_id
  FROM fm_variant_states
  GROUP BY client, product_id
) p
  ON p.client = v.client
  AND p.product_id = v.product_id
SET v.product_variant_count = p.variant_count,
    v.is_parent_variant = CASE
      WHEN p.variant_count > 1 AND v.variant_id = p.parent_variant_id THEN 1
      ELSE 0
    END;
