WP Data Access > Data Explorer > Manage > Settings > Search Settings
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 Tables and Data Projects).
Normal search #
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 | |
| plugin | %plugin% | plugin | * |
| plugin% | %plugin%% | plugin% | * |
| %plugin% | %%plugin%% | %plugin% | |
| 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:
Example 1
select * from customers where customer_name like '%WordPress%'Example 2
select * from customers where customer_name like '%WordPress%'Example 3
select * from customers where customer_name like '%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.
h 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:
Example 4
select * from customers where customer_name like '%WordPress%'Example 5
select * from customers where customer_name like '%WordPress%'Example 6
select * from customers where customer_name like '%WordPress%'Example 4 will never use the index on article_text. Same rule and result as example 1. Example 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 into our search argument (which seems very unlikely for articles as well).
Fulltext search #
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 #
- 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:
- Natural language mode (read more…)
- Boolean mode (read more…)
- With query expansion (read more…)
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 to make any column query-able. Make sure to understand the possibilities and limitations that apply to specific search and column types. Just enable the checkboxes for query-able columns (see image below – click to enlarge).
Limitations #
- 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 option 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 Tables as well as Data Projects. Enable the checkbox “Allow column specific search” (see image below – click to enlarge) to add individual column search.
NOTE Individual columns require 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, data tables and projects.
Adding drop-down lists #
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 drop-down list. To add a drop-down list for a specific column, enable the checkbox “Listbox”.
A drop-down list 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, drop-down lists can increase page load time if a lists contains too many values. Keep your drop-down lists 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 data table requires additional steps. Please follow the link below to learn how to use individual columns search in a data table.
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 #
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 #
Full-text search #
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.
Small tables #
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.
Large tables #
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.
Analysis #
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…
This is a wonderful plugin; thank you! Is it possible to restrict normal exact search to a single column? I have a table with a column for bands and a column for venues. I would like to enable a normal exact search on only the band column.
Thank You!
Thanks for your compliment Andy! 🙂 You can restrict a search to specific columns in the Data Explorer > Manage > Settings > Search Settings: enable query for column band and diable for all other columns.
Hope this helps,
Peter