Efficient Flag-Based Counts in MySQL

Suppose each translator receives several documents and can translate each one into a fixed set of languages. Two translators may work on the same document, but a language must be counted only once per document. The goal is therefore not to count translators or assignments. It is to count distinct document-language pairs.

Consider three translators:

Stephen speaks French, German, and Spanish
  documents 1, 2, 3  → 3 + 3 + 3 translations

Martin speaks German and Spanish
  documents 2, 4     → 0 + 2 additional translations

Ronald speaks French, Polish, and Italian
  documents 1, 5     → 2 + 3 additional translations
------------------------------------------------------
                              16 distinct translations

Document 2 gains nothing new from Martin because Stephen already covers German and Spanish there. On document 1, Ronald adds Polish and Italian, while French was already covered. Across all five documents, the distinct language counts are therefore 5, 3, 3, 2, and 3.

Representing the Fixed Language Set

When the set of languages is small and changes rarely, each language can occupy one bit in an unsigned integer:

Stephen's mask is 1 | 4 | 8 = 13. Martin's is 1 | 8 = 9, and Ronald's is 4 | 32 | 256 = 292. The complete example needs only two tables:

CREATE TABLE translator (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    language_mask INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE document_translator (
    document_id INT UNSIGNED NOT NULL,
    translator_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (document_id, translator_id),
    KEY idx_translator_document (translator_id, document_id),
    CONSTRAINT fk_document_translator_translator
        FOREIGN KEY (translator_id) REFERENCES translator (id)
);

INSERT INTO translator (id, name, language_mask) VALUES
    (1, 'Stephen', 13),
    (2, 'Martin',   9),
    (3, 'Ronald', 292);

INSERT INTO document_translator (translator_id, document_id) VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 2), (2, 4),
    (3, 1), (3, 5);

The composite primary key prevents duplicate assignments and starts with document_id, matching the grouping key used below.

Combining Existing Flag Columns

If the table currently has one TINYINT column per language, add the mask first and populate it with explicit bit positions:

ALTER TABLE translator
ADD language_mask INT UNSIGNED NOT NULL DEFAULT 0;

UPDATE translator
SET language_mask =
      (lang_de << 0)
    | (lang_en << 1)
    | (lang_fr << 2)
    | (lang_es << 3)
    | (lang_be << 4)
    | (lang_pl << 5)
    | (lang_cz << 6)
    | (lang_nl << 7)
    | (lang_it << 8);

Keep the old columns until the masks and their bit counts have been checked. The explicit shifts are easier to audit than a deeply nested expression and make the mapping visible in the migration itself.

Count the Union of Flags

BIT_OR() combines the language masks of every translator assigned to a document. A bit is set in the result if at least one translator has that language. BIT_COUNT() then counts the set bits:

SELECT
    dt.document_id,
    BIT_COUNT(BIT_OR(t.language_mask)) AS translation_count
FROM document_translator AS dt
JOIN translator AS t ON t.id = dt.translator_id
GROUP BY dt.document_id
ORDER BY dt.document_id;

+-------------+-------------------+
| document_id | translation_count |
+-------------+-------------------+
|           1 |                 5 |
|           2 |                 3 |
|           3 |                 3 |
|           4 |                 2 |
|           5 |                 3 |
+-------------+-------------------+

Summing the per-document counts produces the requested total:

SELECT SUM(per_document.translation_count) AS translations
FROM (
    SELECT
        dt.document_id,
        BIT_COUNT(BIT_OR(t.language_mask)) AS translation_count
    FROM document_translator AS dt
    JOIN translator AS t ON t.id = dt.translator_id
    GROUP BY dt.document_id
) AS per_document;

+--------------+
| translations |
+--------------+
|           16 |
+--------------+

What the Normalized Alternative Costs

The conventional design stores one row per translator and language:

CREATE TABLE translator_language (
    translator_id INT UNSIGNED NOT NULL,
    language_code CHAR(2) NOT NULL,
    PRIMARY KEY (translator_id, language_code),
    KEY idx_language_translator (language_code, translator_id),
    FOREIGN KEY (translator_id) REFERENCES translator (id)
);

That model is flexible, but the counting query must expand every document assignment into all languages spoken by its translator and then remove duplicates:

SELECT SUM(per_document.language_count) AS translations
FROM (
    SELECT
        dt.document_id,
        COUNT(DISTINCT tl.language_code) AS language_count
    FROM document_translator AS dt
    JOIN translator_language AS tl
        ON tl.translator_id = dt.translator_id
    GROUP BY dt.document_id
) AS per_document;

In the sample data, the bitmask query joins and aggregates the seven document assignments. The normalized query expands those same assignments to 19 translator-language rows before reducing them to the 16 distinct document-language pairs. If there are D assignments and translators speak k languages on average, the first aggregation processes roughly D rows; the normalized form processes roughly D × k rows and additionally performs distinct-value bookkeeping per document. Depending on the execution plan and available indexes, that may require an internal temporary table, sorting, or index-backed duplicate elimination.

The bitmask does not make low-cardinality flag indexes more selective. It avoids needing those indexes in the first place: each assignment contributes one integer, and the aggregate performs one fixed-width OR operation. The primary key on (document_id, translator_id) also presents assignments in the same order as the grouping key.

When the Bitmask Is the Better Model

This is a strong solution when the flag vocabulary is small, stable, and used mainly as a set: protocol capabilities, fixed permissions, supported output formats, or a controlled list of languages. It reduces storage, keeps the hot query compact, and turns union-plus-distinct-count into two native MySQL operations.

Normalization remains preferable when languages are created dynamically, need their own metadata or foreign keys, are queried individually in many different ways, or may exceed the mask width. INT UNSIGNED provides 32 bits; BIGINT UNSIGNED extends the technique to 64. Beyond that, or when schema flexibility matters more than this aggregation cost, the normalized table is the clearer design.

For production data, compare both versions with EXPLAIN ANALYZE. The important measurements are rows entering the aggregation, temporary-table or sorting work, and elapsed time on representative document and translator distributions.