MySQL full text search ranking

Switch to InnoDB, and

• Set innodb_buffer_pool_size to at least 250M
• Show us the tentative SELECTs
• SHOW CREATE TABLE (DESC is inadequate)

After a few days of fiddling around, I finally managed to get this done. Here is the code I ended up with, packed into a stored procedure. For this to work, I added another table, which just holds a document ID and its length in words.

ALTER PROCEDURE [dbo].[BuildIndexBM25]
-- default parameters K1=> [1.2 - 2.0], B => [0.0 - 1.0], Weight => 3 (e.g. titles only)
    @K1 FLOAT = 1.2,
    @B FLOAT = 0.75,
    @Weight FLOAT = 3
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @N FLOAT;
    DECLARE @AVGDL FLOAT;

    -- number of all documents
    SELECT @N = COUNT(DISTINCT DocumentID) FROM TF;

    -- average document length (in words)
    SELECT @AVGDL = AVG(Length) FROM DocumentLength; 

    -- BM25 implementation
    -- result set: | DocumentA | DocumentB | BM25 |
    SELECT 
        DL.ID AS DocumentA,
        BM.DocumentID AS DocumentB, 
        BM.BM25 AS BM25
    FROM (
        SELECT ID FROM DocumentLength
    ) DL
    CROSS APPLY (
        SELECT
                Q.DocumentID,
                SUM(LOG((@N - X.Count + 0.5) / (X.Count + 0.5)) * (Q.Count * (@K1 + 1)) / (Q.Count + @K1 * (1 - @B + (@B * L.Length / @AVGDL)))) AS BM25
        FROM    TF D, TF Q, DF X, DocumentLength L
        WHERE   D.TermID = Q.TermID
            AND D.DocumentID = DL.ID
            AND Q.TermID = X.ID
            AND D.DocumentID = L.ID
            AND Q.Weight = @Weight 
            AND D.Weight = @Weight
        GROUP BY Q.DocumentID
    ) BM
    WHERE   DL.ID != BM.DocumentID
END

I hope this answer helps anyone who wanted to implement a full-text search algorithm by themselves. The default algorithm in SQL Server 2008 R2 has only minor differences to this implementation.