WP Data Access > Data Explorer > Manage > Settings > Search Settings
- Normal search
- Fulltext search
- Defining queryable columns
- Individual column search
- Search engine like search
- Indexes and performance
The premium version of WP Data Access offers a number of advanced search options (see image below – click to enlarge). Search settings are defined on table level and apply to all plugin tools (Data Explorer, Data Publisher and Data Projects).
This is a premium feature.
The plugin supports the following normal search features:
- Normal wildcard search (plugin default)
- Normal exact search
Normal search performs a standard SQL search, which allows wildcards for CHAR, VARCHAR and TEXT columns.
Normal wildcard versus normal exact search
These search types are basically the same, but there is one important difference. Normal wildcard search performs a wildcard search per default. Normal exact search allows to use wildcards in search arguments, but it does not add wildcards to search arguments on its own.
This is how the plugin processes search arguments (% = wildcard character):
|Search argument||Normal wildcard search||Normal exact search|
|never uses indexes||* uses index (if available)|
Normal exact search will compare a column value with the search argument entered by the user. If the user adds a wildcard (% character), a wildcard search will be performed.
Normal search limitations
- Wildcards can only be used for CHAR, VARCHAR and TEXT columns. Wildcards searches are not possible on binary, numeric, date and time columns.
Normal search strategies
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).
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 fulltext search comes in place.
Fulltext search allows us to use fulltext index tables where the words of our article text are stored in such a way that it improves performance. With fulltext 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 fulltext index on column article_text to speed up performance.
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 fulltext 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, fulltext search might be interesting for smaller columns as well.
In general, fulltext 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.
Fulltext search limitations
- Fulltext search is only asvailable for InnoDB and MyISAM tables.
- Fulltext search can be enabled for views as long for columns containing a fulltext index.
- Fulltext search is available for text column only! It is not possible to create a fulltext index on numeric, binary, date and time columns.
- The plugin does not allow to mix normal and fulltext 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 fulltext search
To add fulltext 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 “Fulltext search” (click image below to enlarge)
For every column to be indexes a fulltext index must be created first. Click the create fulltext icon for every column to create the indexes. All fulltext indexes must be created before the fulltext search setting can be enabled (saved). You can leave the page while a fulltext index is being created in the background and save your settings later.
Fulltext search options
A fulltext 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 fulltext 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!
Define queryable columns
The plugin performs a search on CHAR, VARCHAR and TEXT columns per default. The premium version allows plugin users make any column queryable. Make sure to understand the posiblities and limitations that apply to specific search and column types. Just enable the checkboxes for queryable columns (see image below – click to enlarge).
- Wildcards are only supported on string based columns (char, varchar, text, enum, set, and so on). Other data types will ALWAYS perform an exact search.
- Binary, numeric, date and time columns cannot be queryable if full-text search is enabled.
Individual column search
This options allows users to perform a search on individual columns, while the global search box remains available as well. This feature can be used in normal and fulltext mode and applies to Data Publications as well as Data Projects. Enable checkbox “Allow column specific search” (see image below – click to enlarge) to add individual column search.
NOTE Individual columns requires server-side processing (which is the default). Don’t set serverSide to false!
This adds individual column search for all queryable columns to the Data Explorer, publications and projects.
By default the plugin adds standard input fields to support individual column search (see image above – click to enlarge). For columns having just a few distinct values, the input field can be converted to a listbox. To support a listbox for a specific column, enable the checkbox “Listbox”.
A listbox has the advantage that the user cannot enter a search argument that does not exist. This decreases the change of empty result sets and makes your search engine more efficient. On the downside, listboxes can increase page load time if a listbox contains too many values. Keep your listboxes small!
Restricting searchable columns
By default searchboxes will be added for all searchable columns if individual column search is enabled. This can be restricted by unchecking checkbox Individual for specific columns (see image above – click to enlarge).
Individual column search in the Data Explorer
This works similar in Data Projects!
Using individual column search in a publication requires additional steps. Please follow the link below to learn how to use individual columns search in a publication.
Notes on individual column search
- Global search and individual column search can be combined
- Global search adds an OR condition
- Individual column search adds an AND condition
Search engine like search
Checkbox “No search condition = show no rows” gives a more Google like search experience. Search tables show a result without the need to enter a search argument by default. This option forces the user to perform a search before seeing any results. There is no further documentation on this topic. Just enable it and test!
Indexes and performance
Using indexes to improve performance can be a challenging tasks! But it can give a serious boost to your search pages as well. The question when to use which indexes cannot be answered easily. It depends on many factors, like:
- System resources
- Number of rows in a table
- Table structure
- Number of concurrent users
- And many more…
It are often not those factors on its own, but the combination of them that makes indexing such a tough and interesting issue. Here are some considerations that might help. Keep in mind that every situation is different and needs an analysis on its own.
Normal exact search
With normal exact search, indexes can speed up your performance. But don’t forget that every index has to be maintained as well. Adding an index to every column to improve search performace, might lead to slower inserts, deletes and updates. Ask yourself: is this table mainly queried or mainly edited? Add only indexes to columns when you need them.
Normal wildcard search
It makes no sense to create indexes with normal wildcard search. They will not be used.
Full-text search improves performance for large text columns and large tables having text columns for which wildcard searching is required. Be aware that full-text index tables have to be maintained as well. Every insert, delete and update forces your database management system to update your full-text index tables. Full-text indexes are probably more efficient if you mainly query a table. If you mainly edit a table, a full-text index might slow down your server.
If your tables are small and you have sufficient memory, you might prefer to not use any indexes at all. If your result set can be cached completely in memory, subsequent queries will be fast even without indexes.
But what is a small table? And what is sufficient memory?
Remember I said “it is the combination of factors that makes indexing such a tough and interesting topic”? If you have 1000 page views per day and mostly no concurrent users, just forget about indexes. If you have 1000 page per minute and 100 concurrent users, you’ll need indexes to keep your website running! In other words: analyse.
Analyse your situation before you start to create indexes. If wildcards searching is required, you’ll probaly need indexes. But keep in mind that your indexes are not used with normal wildcard searching. If normal exact searching is not an option, full-text search might help you out.
If possible, perform your analysis on your production server. It makes no sense to perform an analysis on a test machine with no concurrent users and all resources just for you alone.
Perform your analysis on a server with the same characteristics if you cannot perform your analysis on your production server. Use tools to generated requests to stress test.
Most important: analyse how users perform searches! It makes no sense to enter a query just to see if you’re getting results. Your developer queries are usually too optimistic. Real users have different needs. They dont think about the technical consequences. Talk to your users! Let them be your teachers…