How Can I Perform Efficient Full-Text Search on InnoDB Tables Using MyISAM and Stored Procedures?

Fulltext-esque Search on InnoDB Using MyISAM and Stored Procedures

Finding a quick and efficient way to perform fulltext searches on an InnoDB table can be a challenge. While tools like Sphinx provide a solution, this article explores a pure MySQL approach using MyISAM and stored procedures.

The Challenge

A simple query that searches for multiple strings in a row using LIKE statements can be problematic due to its slow performance and limitations.

SELECT ... WHERE row LIKE '%some%' OR row LIKE '%search%' OR  row LIKE '%string%'

The Solution

The proposed solution involves creating a MyISAM fulltext table that indexes back into the InnoDB tables. This allows for indexing a specific column (e.g., subject) that requires searching.

Database Structure

The InnoDB tables are defined for the primary data, such as users and threads.

create table users (...) engine=innodb;

create table forums (...) engine=innodb;

create table threads (
...
subject varchar(255) not null, -- column we want to search
...
)
engine=innodb;

The MyISAM fulltext table (e.g., threads_ft) provides the search index.

create table threads_ft (
...
subject varchar(255) not null,
fulltext (subject), -- fulltext index on subject
...
)
engine=myisam;

Stored Procedure

The stored procedure (e.g., ft_search_threads) accepts the search term as input and performs the search using the MyISAM fulltext index. It then joins the results with the InnoDB tables to retrieve the necessary data.

create procedure ft_search_threads (
in p_search varchar(255)
)
begin
...
select
 t.*,
 ...
from
 threads_ft tft
...
match(tft.subject) against (p_search in boolean mode) 
...
order by 
 rank desc
...
end;

Example Usage

The stored procedure can be called from a PHP application or a command-line tool:

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

This example returns the top 100 results from the threads table that match the search term "innodb clustered index."

By utilizing this approach, you can achieve fulltext-like search functionality on InnoDB tables without compromising performance. It involves creating a dedicated MyISAM table for indexing and writing a stored procedure to perform the search and data retrieval.

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