Make MySQL searches faster with full text index

Just a simple web developer who prefers to work with WordPress and PHP. He is a freelance and employed web developer from Germany. Now tries to write here regularly and uses deepl to translate for it.

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 INSERT and 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 AND or 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!

Make MySQL searches faster with full text index

Comments

More from this blog

Remove defined HTML tags including content from a string with PHP

Create and log in a WordPress administrator with FTP

SQL: Repair Auto Increment of a Table

WordPress: Useful functions for working with arrays and objects

Provide column with full text index

Search column with full text index

When you should not use a full text index

Command Palette

Comments

More from this blog

Provide column with full text index

Search column with full text index

When you should not use a full text index