Can InnoDB Tables Leverage Full-Text Search Functionality Without External Tools?

Can InnoDB Utilize Fulltext Search-Like Functionality?

A simple query with multiple LIKE operators can become inefficient when searching for multiple search strings. To remedy this on an InnoDB table without utilizing external tools like Sphinx, consider adopting a MyISAM fulltext table to index back into your InnoDB tables.

Database Schema

Create your database using InnoDB as the storage engine:

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, -- Desired search field
    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) -- Composite clustered PK index
) ENGINE=INNODB;

Next, create the fulltext search table using MyISAM:

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), -- Fulltext index on subject
    PRIMARY KEY (forum_id, thread_id) -- Composite non-clustered index
) ENGINE=MYISAM;

Search Stored Procedure

Finally, create a stored procedure for your fulltext search:

DROP PROCEDURE IF EXISTS ft_search_threads;
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;

CALL ft_search_threads('+innodb +clustered +index');

With these modifications, you can effectively implement fulltext-like search capabilities on your InnoDB tables without resorting to external solutions.

The above is the detailed content of Can InnoDB Tables Leverage Full-Text Search Functionality Without External Tools?. For more information, please follow other related articles on the PHP Chinese website!