TF-IDF Related-Article Ranking with MySQL 8.4

Related-article recommendations do not require a black-box search service. A page can be represented as a sparse vector of weighted terms, and two pages can be compared by the angle between their vectors. The resulting score is explainable: pages are similar when they share terms that are important inside those pages and uncommon across the complete collection.

The implementation below follows a practical publishing pipeline. PHP extracts words from the path, HTML, and optional stage content. MySQL stores term counts, computes normalized TF-IDF weights, joins documents through their shared terms, and writes the three strongest matches into site.SID_Related. The ranking is generated offline, so page requests only need to load the already selected IDs.

The existing generator calculates the same weights in PHP and inserts only the normalized TFIDF value. To make the database algorithm inspectable, the version here retains TermCount and moves the weighting and normalization steps into MySQL. Given the same document-term counts, both decompositions implement the formulas below; only ordinary floating-point rounding can differ.

Define the Exact Weighting Model

Let f(d,t) be the number of occurrences of term t in document d. Raw counts let a repeated word grow without limit, although its tenth occurrence usually contributes less information than its first. The logarithmic term-frequency weight used here reduces that effect:

\[\operatorname{tf}(d,t)=1+\log_{10} f(d,t)\]

There is no row for a term with frequency zero, so the logarithm is evaluated only for positive counts. Next, let N be the number of indexed documents and n(t) the number of documents containing the term. Inverse document frequency assigns less weight to terms occurring throughout the collection:

\[\operatorname{idf}(t)=\log_{10}\!\left(\frac{N}{n(t)}\right)\]

A term appearing in every document has an IDF of zero and therefore cannot make two documents distinctive. The unnormalized TF-IDF coordinate is the product

\[w(d,t)=\operatorname{tf}(d,t)\operatorname{idf}(t).\]

Longer pages contain more terms and would otherwise tend to receive larger dot products. L2 normalization divides each coordinate by the Euclidean length of its document vector:

\[\widehat{w}(d,t)=\frac{w(d,t)}{\sqrt{\sum_u w(d,u)^2}}.\]

The normalized vectors have length one. Their dot product is therefore their cosine similarity:

\[\operatorname{similarity}(a,b)=\sum_t\widehat{w}(a,t)\widehat{w}(b,t).\]

All coordinates in this model are nonnegative, so scores lie between zero and one. A score of zero means that the documents share no positively weighted term. The number is a relative similarity within the current corpus, not a probability. Adding, removing, or substantially changing pages changes document frequencies and can change every ranking.

Choose What Constitutes a Document

The retrieval unit must match the item eventually recommended. Here, one canonical site page is one document. Its text consists of the relative path and the rendered article source. If a matching .stage file exists, its content is appended to the same document rather than indexed as a second result.

The path is useful evidence: a path such as software/databases/tf-idf-with-mysql.html contributes the topic words even when the prose uses them sparingly. This is a deliberate field-weighting decision. Remove the path from the input if URL vocabulary should have no influence.

Scripts and styles must be removed before tokenization. Otherwise JavaScript identifiers, CSS declarations, and repeated interface scaffolding can dominate the article prose. HTML tags are removed as well, punctuation becomes a separator, and words are lowercased. Terms with one character, numeric-only tokens, and common stopwords are discarded. The same normalization must be used for every page and every rebuild.

function extractTerms(string $relativePath, string $html, ?string $stage): array {
  $text = $relativePath . ' ' . $html . ' ' . ($stage ?? '');
  $text = preg_replace('~<script\b[^>]*>.*?</script>~is', ' ', $text);
  $text = preg_replace('~<style\b[^>]*>.*?</style>~is', ' ', $text);
  $text = strip_tags($text);
  $text = html_entity_decode($text, ENT_QUOTES | ENT_HTML5, 'UTF-8');
  $text = mb_strtolower($text, 'UTF-8');
  $text = preg_replace('/[^\p{L}\p{N}]+/u', ' ', $text);

  $words = preg_split('/\s+/u', trim($text), -1, PREG_SPLIT_NO_EMPTY);

  return array_values(array_filter($words, static function (string $word): bool {
    global $stopwords;
    return mb_strlen($word, 'UTF-8') > 1
      && !is_numeric($word)
      && empty($stopwords[$word]);
  }));
}

A stopword set should be stored as keys mapped to true, making each lookup constant-time. Token counts for a document can then be produced with array_count_values(). Keep that count: MySQL needs it to calculate the logarithmic term-frequency component.

Store a Sparse Document-Term Matrix

A dense matrix would contain one cell for every document-term combination, almost all of them zero. The junction table stores only observed combinations. The binary collation gives each already-normalized token an exact identity, while the unique key prevents duplicate term rows.

This is a rebuild schema for the MySQL-based variant, not the generator's original two-column working table. The added TermCount column is what lets MySQL calculate TF, while the composite primary key makes the document-frequency count unambiguous.

CREATE TABLE term (
  TID   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  TTerm VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (TID),
  UNIQUE KEY term_value (TTerm)
);

CREATE TABLE site_term (
  S_ID      BIGINT UNSIGNED NOT NULL,
  T_ID      BIGINT UNSIGNED NOT NULL,
  TermCount INT UNSIGNED NOT NULL,
  TFIDF     DOUBLE NOT NULL DEFAULT 0,
  PRIMARY KEY (S_ID, T_ID),
  KEY term_documents (T_ID, S_ID)
);

The primary key supports all terms of one page and guarantees one count per coordinate. The reverse index supports the similarity self-join, which begins with a term and finds every pair of documents containing it. DOUBLE is preferable to a narrow decimal type because logarithms, square roots, normalization, and accumulated dot products are floating-point calculations.

During corpus traversal, resolve the canonical page's SID from its path, count the extracted words, and load one coordinate per distinct term. LAST_INSERT_ID(TID) makes the term upsert return the existing ID as well as a newly allocated one. The coordinate insert intentionally has no duplicate handler: a repeated (S_ID,T_ID) pair indicates a loader error and should violate the primary key.

$insertTerm = $pdo->prepare('
  INSERT INTO term (TTerm)
  VALUES (?)
  ON DUPLICATE KEY UPDATE TID = LAST_INSERT_ID(TID)
');

$insertCoordinate = $pdo->prepare('
  INSERT INTO site_term (S_ID, T_ID, TermCount)
  VALUES (?, ?, ?)
');

$pdo->beginTransaction();

try {
  foreach ($documents as $document) {
    $terms = extractTerms(
      $document['path'],
      $document['html'],
      $document['stage'] ?? null
    );

    foreach (array_count_values($terms) as $term => $termCount) {
      $insertTerm->execute([$term]);
      $termId = (int) $pdo->lastInsertId();
      $insertCoordinate->execute([
        $document['siteId'],
        $termId,
        $termCount,
      ]);
    }
  }

  $pdo->commit();
} catch (Throwable $exception) {
  $pdo->rollBack();
  throw $exception;
}

Build $documents from the same traversal that reads the relative path, HTML, and optional stage file. If extraction or insertion fails, roll back the transaction and discard the working tables. After a successful load, TermCount is positive and each (S_ID,T_ID) pair occurs exactly once.

Compute TF-IDF in MySQL

Document frequency is the count of site_term rows per term because the primary key permits at most one row per document and term. Materializing it once avoids repeating the same aggregate for every coordinate.

SET @document_count = (
  SELECT COUNT(DISTINCT S_ID)
  FROM site_term
);

CREATE TEMPORARY TABLE term_document_frequency AS
SELECT
  T_ID,
  COUNT(*) AS document_frequency
FROM site_term
GROUP BY T_ID;

ALTER TABLE term_document_frequency
  ADD PRIMARY KEY (T_ID);

UPDATE site_term AS st
JOIN term_document_frequency AS df USING (T_ID)
SET st.TFIDF =
  (1 + LOG10(st.TermCount))
  * LOG10(@document_count / df.document_frequency);

The corpus must contain at least one loaded document. Terms present in every document receive zero as expected. A page containing only such terms has a zero-length vector; it cannot be normalized and has no useful recommendation signal. The normalization update deliberately leaves those zero coordinates unchanged.

CREATE TEMPORARY TABLE document_norm AS
SELECT
  S_ID,
  SQRT(SUM(TFIDF * TFIDF)) AS l2_norm
FROM site_term
GROUP BY S_ID;

ALTER TABLE document_norm
  ADD PRIMARY KEY (S_ID);

UPDATE site_term AS st
JOIN document_norm AS norm USING (S_ID)
SET st.TFIDF = st.TFIDF / norm.l2_norm
WHERE norm.l2_norm > 0;

DROP TEMPORARY TABLE document_norm;
DROP TEMPORARY TABLE term_document_frequency;

Computing a norm once per page is significant. Recalculating the sum of squared coordinates for every term turns the normalization of a document with m distinct terms from linear work into quadratic work. Materializing the norm preserves the formula while avoiding that repetition.

Calculate Pairwise Cosine Similarity

The self-join pairs documents only through terms they share. For each ordered pair, summing the products of matching normalized coordinates gives the cosine similarity. The a.S_ID <> b.S_ID condition excludes a page's perfect match with itself.

SELECT
  a.S_ID AS source_id,
  b.S_ID AS related_id,
  SUM(a.TFIDF * b.TFIDF) AS similarity
FROM site_term AS a
JOIN site_term AS b
  ON b.T_ID = a.T_ID
 AND b.S_ID <> a.S_ID
WHERE a.TFIDF > 0
  AND b.TFIDF > 0
GROUP BY a.S_ID, b.S_ID
ORDER BY source_id, similarity DESC, related_id;

The result contains both directions. That is necessary because the final ranking is partitioned by source page. The similarity value itself is symmetric, but page b can be among the best three matches for page a without page a being among the best three for page b.

Select and Persist the Top Three

MySQL 8.4 can rank each source page's candidates with ROW_NUMBER(). Ordering by the related ID after the score makes ties deterministic. The outer aggregation turns the three rows into the comma-separated representation expected by site.SID_Related.

UPDATE site AS destination
LEFT JOIN (
  SELECT
    source_id,
    GROUP_CONCAT(
      related_id
      ORDER BY similarity DESC, related_id
      SEPARATOR ','
    ) AS related_ids
  FROM (
    SELECT
      pair_scores.*,
      ROW_NUMBER() OVER (
        PARTITION BY source_id
        ORDER BY similarity DESC, related_id
      ) AS related_rank
    FROM (
      SELECT
        a.S_ID AS source_id,
        b.S_ID AS related_id,
        SUM(a.TFIDF * b.TFIDF) AS similarity
      FROM site_term AS a
      JOIN site_term AS b
        ON b.T_ID = a.T_ID
       AND b.S_ID <> a.S_ID
      WHERE a.TFIDF > 0
        AND b.TFIDF > 0
      GROUP BY a.S_ID, b.S_ID
    ) AS pair_scores
  ) AS ranked
  WHERE related_rank <= 3
  GROUP BY source_id
) AS selected
  ON selected.source_id = destination.SID
SET destination.SID_Related = selected.related_ids;

The LEFT JOIN is intentional. A page with no positively weighted overlap receives NULL instead of retaining recommendations from an earlier corpus. With at most three unsigned IDs, the configured VARCHAR(32) representation is sufficient for 32-bit IDs. If IDs can exceed ten decimal digits or more recommendations are stored, enlarge the column or use a relational recommendation table.

After updating site, the working tables can be removed. They are a generated index, not source data:

DROP TABLE site_term;
DROP TABLE term;

Verify the Generated Index

A successful query is not enough. Check the mathematical invariants before publishing the result. Every nonzero document vector should have squared length approximately one; a small tolerance is required because the weights are floating point values.

SELECT
  S_ID,
  SUM(TFIDF * TFIDF) AS squared_length
FROM site_term
GROUP BY S_ID
HAVING MAX(TFIDF) > 0
   AND ABS(SUM(TFIDF * TFIDF) - 1) > 1e-10;

This query should return no rows. Inspect candidate scores before discarding the working tables, especially pages with no matches, pages dominated by one rare term, and pairs tied at the third rank. Also verify that every stored ID exists and that a page never recommends itself.

SELECT SID, SID_Related
FROM site
WHERE SID_Related IS NULL
   OR SID_Related = '';

SELECT COUNT(*) AS invalid_term_counts
FROM site_term
WHERE TermCount = 0
   OR TFIDF < 0;

Understand Cost and Failure Modes

The expensive operation is the term-based self-join. A term occurring in k documents contributes k(k-1) ordered pairs before aggregation. Stopword removal is therefore both a relevance rule and a substantial performance optimization: ubiquitous words create many pairs while carrying little or zero IDF value. Filtering zero weights before grouping avoids work that cannot change a score.

Repeated navigation labels, legal boilerplate, scripts, and styles can make unrelated pages appear similar. Excluding non-content pages and stripping non-prose blocks before counting terms prevents much of that contamination. Conversely, aggressive punctuation replacement can merge or discard meaningful technical notation, so inspect the resulting term table rather than treating tokenization as invisible plumbing.

TF-IDF statistics are global. Updating only one changed page leaves old IDF values on every other page and produces an internally inconsistent matrix. Rebuild term counts, document frequencies, normalized weights, and recommendations as one complete generation process. The temporary document-frequency and norm tables must remain on the same MySQL connection as their updates.

Finally, relatedness is not editorial correctness. The score measures shared weighted vocabulary. A deterministic top-three list, a few fixed regression pages, and manual inspection of surprising pairs make changes to stopwords and tokenization reviewable without pretending that the cosine score understands the articles.

References

• [Salton1988]Gerard Salton and Christopher Buckley, Term-weighting approaches in automatic text retrieval, Information Processing & Management 24(5), 1988.
• [MySQL-Math]MySQL 8.4 Reference Manual: Mathematical Functions.
• [MySQL-Window]MySQL 8.4 Reference Manual: Window Function Descriptions.
• [MySQL-Aggregate]MySQL 8.4 Reference Manual: Aggregate Function Descriptions.