Gordian Labs Company Blog » Using MySQL Fulltext Search Engine with Data Driven Web Applications

In part I, we discussed the basics of implementing a simple search functionality using the MySQL fulltext search index. In this post, we will build on those concepts, exploring paging, sorting, mixed data search, filtering and InnoDB.

Paging and Sorting

Often our search results are far too large to fit on a single results page. Wrapping the results in a paging interface is the obvious solution to this. Define a page size, divide up your results by this page size to get pages, and then use the limit and offset syntax in your SQL statement to query the results for any given page.

select id, description from product_descriptions

where MATCH(description) AGAINST ('"+$processed_search+"' IN BOOLEAN MODE)

limit "+$page_size+" offset ("+($page*$page_size)+");

In this code fragment, we add two variables, $page and $page_size. You determine the number of pages by dividing your total search resultset size by the pagesize, rounded up. This will give you the basic building blocks of a search result paging system.

Problems
There are a few problems with the simple example above. The main problem is that we are redoing the fulltext search every time we change pages. Depending on your application, the data we are searching can change, altering your result count and paging math and causing us to lose results in the cracks. It is also rather costly to both do the count of total search results as well as repeat the fulltext search each time we change pages. What we really want to do is perform the search once, save the results in a temporary table, and then do our paging and sorting on an unchanging snapshot of the results at the time you executed your search.

Search Snapshot Tables

We will need two new tables to store our search snapshots: Snapshots and snapshot_results.



create table search_snapshots

(

id int primary_key,

search_text varchar(1024) not null,

search_size int not null default 0,

search_date datetime not null default now()

);

create table search_snapshot_results (

search_snapshot_id int not null,

product_id int not null);

With these tables in place, we can do a better paging implementation. Instead of running the search query and displaying the results on our webpage for every page load, we instead do the following:

On search execution, insert a new record into search_snapshot table. insert the search text (thats all you have) and store the ID of the record created.
run your search query, but instead of just doing a normal select, do a select into statement, populating the search_snapshot_results table with the search id from step 1 and the product_id. note the rowcount of this statement
update the search_snapshot table with the result count from step 2.
forward the user to your search pages, but instead of running your fulltext search, instead load your results from the snapshot table, using the search_snapshot_id as your primary key, and joining search_snapshot_results to products to retrieve your content.

You now will have a faster and more reliable search result set to page over. Depending on your application, you can keep these snapshots around so that links into your search results will continue to work, or you may periodically delete the snapshots and results after a timeperiod has passed.

Sorting these results is now a simple matter as well, by simply adding order by statements to your query of search_snapshot_results.

General Purpose Search Table

You probably noticed that the search_snapshot_results table only had a key reference to the Product table, as opposed to having all the date right there. With a small modification, we could use the search_snapshot_table to store the search results of any content (or mix of content) that you may have in your database.



create table search_snapshot_results (

search_snapshot_id int not null,


result_type varchar(16),

result_frn_id int not null);

This new table can store results from any search you execute, you just need to specify a label in the result_type field to describe what table you should join to through result_frn_id. With this in place, you can reuse your filtering and paging code for any search type you’d like. The only thing you need to do is modify the join when rendering your results to match the result_type.

InnoDB

One of the big annoyances of the Fulltext search system is that it is currently MyISAM only, no InnoDB support. To get around this, I uses the following trick. Its not the most elegant or efficient solution, but it works. You may mix InnoDB and MyISAM table engines within a database, so I create the following table as a MyISAM table.

create table text_index (
id int unsigned primary key,
type varchar(16) not null,
frn_id int unsigned not null,
content LONGTEXT not null,
FULLTEXT(content)
);

This table’s purpose is to store the searchable text of an InnoDB record, index it, and keep track of the id of the record it is representing. When you do a fulltext search of this table, you specify your search term, and the TYPE of data you are searching and it will return a full list of ids in your InnoDB table for the type you specified. It is quite easy to populate your search_snapshot right out of a query of this table.

Now here is where having a properly abstracted data access layer in your project is useful. For the previous system to work, you need to find every place you can change the data you are able to search, and make sure you refresh the record in text_index as well. This means that every insert, update, and delete statement to Product table would need to be accompanied by an insert, update, or delete in the text_index as well to keep your search as accurate as possible. An easier solution, though less accurate, is to have a timed job that routinely deletes all records from text_index for a particular type, and then performs something like the following to refresh the index.

delete from text_index where TYPE='PRODUCT';

insert into text_index (type, frn_id, content)
select 'PRODUCT',product_id, product_description
from product_descriptions;

This will repopulate the search index for that table/type. It is a useful way of seeding your index
at the beginning as well.

Using MySQL Fulltext Search Engine with Data Driven Web Applications – Part II

No Comments