How Can I Implement Full-Text Search on InnoDB Tables Using MyISAM for Indexing?

Fulltext-Like Search on InnoDB: An Alternative Approach

While full-text search is natively supported in MyISAM tables, InnoDB users may face limitations when attempting to implement similar functionality. To address this challenge, an intriguing solution emerges that involves utilizing a MyISAM full-text table to index back into an InnoDB database.

Creating InnoDB Tables

The initial step involves establishing the necessary InnoDB tables. These tables will serve as the core data repositories. For instance, consider the following schema:

CREATE TABLE users (...) ENGINE=InnoDB;

CREATE TABLE forums (...) ENGINE=InnoDB;

CREATE TABLE threads (
    forum_id SMALLINT UNSIGNED NOT NULL,
    thread_id INT UNSIGNED NOT NULL DEFAULT 0,
    user_id INT UNSIGNED NOT NULL,
    subject VARCHAR(255) NOT NULL,
    created_date DATETIME NOT NULL,
    next_reply_id INT UNSIGNED NOT NULL DEFAULT 0,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (forum_id, thread_id)
) ENGINE=InnoDB;

Establishing a Full-Text Table

Next, create a MyISAM table dedicated to full-text indexing. This table will facilitate the indexing of specified InnoDB table fields.

CREATE TABLE threads_ft (
    forum_id SMALLINT UNSIGNED NOT NULL,
    thread_id INT UNSIGNED NOT NULL DEFAULT 0,
    subject VARCHAR(255) NOT NULL,
    FULLTEXT (subject),
    PRIMARY KEY (forum_id, thread_id)
) ENGINE=MyISAM;

Maintaining Table Data

To ensure synchronization between the InnoDB and MyISAM tables, implement methods for updating the full-text table. This can be achieved through triggers or nightly batch processes.

Stored Procedure for Searching

To facilitate the full-text-like search functionality, create a stored procedure within MySQL. This procedure will query the MyISAM table, filter the results based on the provided search term, and return the most relevant matches.

DELIMITER #
CREATE PROCEDURE ft_search_threads (
    IN p_search VARCHAR(255)
)
BEGIN
    SELECT
        t.*,
        f.title AS forum_title,
        u.username,
        MATCH(tft.subject) AGAINST (p_search IN BOOLEAN MODE) AS rank
    FROM
        threads_ft tft
    INNER JOIN threads t ON tft.forum_id = t.forum_id AND tft.thread_id = t.thread_id
    INNER JOIN forums f ON t.forum_id = f.forum_id
    INNER JOIN users u ON t.user_id = u.user_id
    WHERE
        MATCH(tft.subject) AGAINST (p_search IN BOOLEAN MODE)
    ORDER BY
        rank DESC
    LIMIT 100;
END;

By calling this stored procedure and providing a search term, users can retrieve relevant results from the InnoDB tables.

This approach presents a viable alternative for implementing fulltext-like search functionality within InnoDB tables, harnessing the strengths of both InnoDB and MyISAM engines.

The above is the detailed content of How Can I Implement Full-Text Search on InnoDB Tables Using MyISAM for Indexing?. For more information, please follow other related articles on the PHP Chinese website!