Make MySQL searches faster with full text index
3 min read
As a rule, searches are created with the MySQL
LIKE. This usually works very well, as long as the database table is not too large. However, if you work with a large database (millions+ entries), then you quickly notice how long a search in it actually takes. The solution here is full text index, at least in most cases. Here you will learn how to provide an existing column for the fulltext index and how to start a fulltext search with
Provide column with full text index
To provide a column with a full text index, the column must be changed by means of
ALTER TABLE. For this you think about a name for the full text index and determine in the course of this directly the column concerned. Usually you use the name of the column as name. But you can name it as you like.
ALTER TABLE my_table ADD FULLTEXT INDEX my_column (my_column);
Here we have now added a full text index to the column
my_column. With a large table, this can take several seconds, or even minutes. But this is usually not a problem, you just have to be patient here.
Search column with full text index
Before we provided the column with a full text index, we searched within the column with the
LIKE statement. In principle, this always works very well, but a wildcard must always be set here so that everything is searched and then everything in the column is really searched. That's why it is basically slower than a full text index in a large table.
$wpdb->prepare("SELECT * FROM my_table WHERE my_column LIKE '%%%s%%'", "My search word");
Important: To be able to use
wpdb->prepare(), we write
%%searchword%% instead of
%searchword%, so that the
$wpdb->prepare() also knows that we are using a wildcard at this point. To be on the safe side, we should also use
$wpdb->esc_like() here. An example of this:
$wpdb->prepare("SELECT * FROM my_table WHERE my_column LIKE '%%s%%'", $wpdb->esc_like("My search word"));
This already works very well and does not cause any problems. It just gets slow at some point. If you don't want to work with full text index, you can also change the table type to InnoDB. This is for larger table, which makes for faster queries, but slows down the
UPDATE commands, among others. You can decide that for yourself.
How does our search look now with a full text index? Quite similar, as you can see:
$wpdb->prepare("SELECT * FROM my_table WHERE MATCH (my_column) AGAINST (%s)", "My search word");
This is how the search with full text index looks like. You can also set more logical operators like
OR as usual. This is no problem with a fulltext index.
Note that you do not need a wildcard. The full text search works completely without wildcard.
When you should not use a full text index
The full text search is slower than the search for a unique term. So if you want to search for example in the
post_id column and have the ID, then the normal search is faster. Simply because you are looking for something exactly and it is exactly in the column.
There are also other examples and cases where it is not so useful. But since this post is more of a thought support, I'll leave it at that for now. Good luck and have fun coding!
Did you find this article valuable?
Support Kevin Pliester by becoming a sponsor. Any amount is appreciated!