Any Way to Achieve Fulltext-Like Search on Innodb

Any way to achieve fulltext-like search on InnoDB

use a myisam fulltext table to index back into your innodb tables for example:

Build your system using innodb:

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, -- gonna want to search this... !!
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;

Now the fulltext search table which we will use just to index back into our innodb tables. You can maintain rows in this table either by using a trigger or nightly batch updates etc.

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;

Finally the search stored procedure which you call from your php/application:

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');

Hope this helps :)

Fulltext Search with InnoDB

I can vouch for MyISAM fulltext being a bad option - even leaving aside the various problems with MyISAM tables in general, I've seen the fulltext stuff go off the rails and start corrupting itself and crashing MySQL regularly.

A dedicated search engine is definitely going to be the most flexible option here - store the post data in MySQL/innodb, and then export the text to your search engine. You can set up a periodic full index build/publish pretty easily, and add real-time index updates if you feel the need and want to spend the time.

Lucene and Sphinx are good options, as is Xapian, which is nice and lightweight. If you go the Lucene route don't assume that Clucene will better, even if you'd prefer not to wrestle with Java, although I'm not really qualified to discuss the pros and cons of either.

Best way to emulate fulltext search in a InnoDB/MySQL database using Codeigniter

The easiest way will be to create an MyISAM table for the text data for the product and run the search queries against it. Then you can rebuild the data in the MyISAM table every day with a cron job, to ensure consistency. Or you can make your models to update the data in the MyISAM table on every update.

mySQL LIKE Query on Full Text

No MySQL indexing method will get you "East Los Angeles" but not "EastLos Angeles", plus the many unstated cases: "Dayton" but not "Daytona Beach", "Forest Hill" but not "Forest Hills", "Portland, ME" but not "Portland, OR". Etc.

You may, however, use a combination of SQL and application code. Searching for "Angeles" using a FULLTEXT index will be very fast, then the application code can deal with what comes around it. Note that "Hill" may match "Hills" in FULLTEXT, so that would be another thing to check. Also, there is a minimum "word length"; if you don't change that, the "Fe" in "Santa Fe Springs" will be ignored. And short names ("Roy", in Utah) could be an issue. Norway has a 1-letter city: "Å".

INNODB FULLTEXT search with JOIN: search term on different tables

As described by @Akina this doesn't seem to be possible because INNODB doesn't allow FULLTEXT search on two tables.

I'll create a third table only for the FULLTEXT search and fill that with the data from both tables.

---

Related Topics