The premium version of WP Data Access supports full-text search. Nice! But what is full-text search…?
When we perform a standard search, our database management system performs a query on the table and/or index tables. Suppose we have a customer table where we store customer information. Column customer_name contains the name of a customer and is indexes. Let’s have a look at some queries on table customer:
select * from customers where customer_name like '%WordPress%'
select * from customers where customer_name like 'WordPress%'
select * from customers where customer_name = 'WordPress'
Example 1 will never use the index on customer_name. The reason is simply because the search argument contains a wildcard (% character) at the beginning. This eliminates the possibility to use indexes in general. Examples 2 and 3 will use the index on column customer_name.
Suppose we have a table articles, which contains scientific articles. The content of these articles is stored in column article_text and can take up to 64 KB space. Let’s presume there is an index on column article_text (which is not very wise) and have a look on some queries on table articles:
select * from articles where article_text like '%WordPress%'
select * from articles where article_text like 'WordPress%'
select * from articles where article_text = 'WordPress'
Example 4 will never use the index on article_text. Same rule and result as example 1. Examples 4 might give a result, but only if the first word of the article is WordPress (which seems very unlikely for an article). Example 6 will use the index on column article_text but will never give any results, unless we enter the whole text to our search argument (which seems very unlikely for articles as well).
Indexing large text columns
From the queries on table articles we have learned that an index on column article_text does not make sense. Users however, want to use keywords to search through articles. And they want to see their query results as fast as possible. This is where full-text search comes in place.
Full-text search allows us to use full-text index tables where the words of our article text are stored in such a way that it improves performance. With full-text search a query looks like this:
select * from articles where match (article_text) against ('WordPress')
This will give us all articles for which column article_text contains the word WordPress, while using the full-text index on column article_text to speed up performance.
Indexing smaller text columns
For a small amount of simple textual data it’s possible to provide basic search functionality via simple string matching. In some cases however, you might want to use full-text indexes for smaller columns as well. This depends on a number of factors that influence performance. If you have a large number of rows and wildcard usage is required, full-text search might be interesting for smaller columns as well.
In general, ful-text search helps you to look for a portion of a text column through fast indexes. In some cases however, a full table scan might be faster.
- Full-text search is only asvailable for InnoDB and MyISAM tables.
- Full-text search is available for text column only! It is not possible to create a full-text index on numeric, binary, date and time columns.
- The plugin does not allow to mix normal and full-text search. Although this is possible, queries mixing these different types of search strategies often lead to poor performance. This is especially the case for servers running on low resources.
Adding full-text search
To add full-text search to a table:
- Go to the Data Explorer
- Click on the Manage link of the table
- Click on tab Settings
- Click on link Search Settings
- Click radio button “Full-text search” (click image below to enlarge)
For every column to be indexes a full-text index must be created first. Click the create full-text icon for every column to create the indexes. All full-text indexes must be created before the full-text search setting can be enabled (saved). You can leave the page while a full-text index is being created in the background and save your settings later.
A full-text search can use different strategies. This depends on the mode, which can be defined per table:
Boolean mode is probably the one most users are used to. It allows to use AND, OR and wildcards (* character) which can be found in many other full-text search engines as well.
Please read the MySQL documentation (follow the read more… links) to understand the possibilities of the different modes and help your users to get the best results. This is a powerful feature with many possibilities!