Official Power Up Hosting Blog

Everything about Linux, Windows, and hosting ;)

I am an avid book reader, who enjoys technology as well as writing.

Share


Our Newsletter


Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Tags


Twitter


Official Power Up Hosting Blog

3 Steps To Improve Database Searches with Full-text search on Ubuntu 16.04

Apurva ChodnekarApurva Chodnekar

Introduction:

When you think about it database searches with Full-text search are actually quite simple to do.

But wait for a second, why do we need full-text search? Why not just use 'like' if all we are going to do is search the database?

To answer that we need to have a match(contest) between full text search and like i.e full text search vs like.

Full-text search (full text search definition): Known by its acronym FTS, Full-text search is a technique.

This technique is used by search engines to find required results in the databases.

That was the basic full text search definition. It's faster and returns relevant results.

Like: It is used for searching strings. Each time we use like to search a database it loads every row in the database for comparison.

It finds the right one and then discards the others. It takes up a lot of memory and time to perform the search.

It is only suitable for small databases with a limited number of rows.

This full text search example will help you understand it in a better way.

If a user searches for "watches and shoes", an application
that has enabled FTS might give the user results that contain the words separately i.e it might return few results for the word "watches" and a few other results for the word "shoes".

That's just one possibility, it might return results in the reverse order "shoes and watches". It gives the user more relevant results at a faster speed.

Prerequisites:

  1. MySQL 5.6 server or any of the higher versions.
    Refer How to install and Secure MySQL on Ubuntu 14.04 and 16.04

  2. An installed Ubuntu 16.04 server. If you haven't installed it refer Ubuntu Server Setup Guide for Beginners (Version 16.04)

We have used an online MySql terminal. This will guide you through every query that you need to perform to get started with full-text search on Ubuntu 16.04.

Steps to improve database searches with full-text search in mysql 5.6 and above on Ubuntu 16.04:

Step 1: To create data for testing.

As we are going to try and improve our database searches with the full-text search, we need to create data for testing.

For that, first we need to get access to the mysql console. It's quite easy, you have to enter the username and password that you set when you installed the mysql console.

mysql -u root -p

When your credentials are accepted you will be connected to MySQL.

In the screenshots you will see MariaDB that's because this is just for the purpose of showing you the steps, so we have used an online MySQL terminal.

You will be connected to mysql and that is what you will see in place of MariaDB in your console.

Next, you have to create a database called testdatadb. Then use that database.

Because we need to add tables and data to that database.

So, rather than doing it with every query it's better to get it done initially.

Full-textsearch

Now:

We will create a table with table name book.
This table has id, book_name, author and quote columns.

DatabaseSearches-1

Create table is the command used to create a table in MySql. Here book is the table name.

We have set all the columns book_name, author, and quote to text. The values in the columns can be of unlimited length.

Here id is the primary key of this table, it has been set to not null. Each and every new row has to have a value.
We have also added the special type auto_increment.

What it does it fill the next row's id field with the id that is available after the current one.

Next up is the insert command we need data to try the full-text search on, so, we will have to add it to the table.

We use insert to get that task done in mysql. It add the data to the table.

fulltext-2

When we need to add data to a specific space "into" is used. For example, we are adding the data to our table book.

As we already know book_name,author and quote are the columns of the table book. The next few rows are the values we have added to the table.

Step 2: To create Full-Text search index using Full-Text search functions.

Now that we have our data, next step is mysql create fulltext index i.e. we need to create index in order to use FTS.

And to create mysql full text index we will have to use the mysql command FULLTEXT. What FULLTEXT does is removes the punctuation and changes the case of uppercase letters to lowercase.

After that perform a full text search mysql.

It is done as shown in the below screenshot.

full_text-1

What we see above is an mysql match against example. Where we have used the functions MATCH() and AGAINST() to perform a full-text search.

With MATCH(), we specify the set of columns that we are indexing. Whereas, with AGAINST() we specify the word we are using full text search for.

It's not actually necessary to mention IN NATURAL LANGUAGE MODE. As by default MySQL assumes the natural language mode.

The purpose of "\G" is to print all the output in a new line.
It ensures that all the data is visible when the columns have lengthy values.

In the displayed results none of the output contains the text "Always there", but as we have used full text search we got relevant results.

id 4 and id 1 have the word "always" in their quote.
And id 2 has the word "there" in the quote.

This is how full-text search works.

Step 3: To get better and refined search results.

We can refine the results in two ways.

The first one we will use the relevance score and the second one will use IN BOOLEAN.

a)With the help of relevance score.

Relevance score makes it easier to find out the most relevant result. We will get the relevance score for the query "percent of all".

We have used a score to name the second column otherwise it would have been named MATCH (book_name, author, quote) AGAINST ('percent of all').

We have the select query as well as the result in the below screenshot.

But it also displays the values of other rows that are not relevant at all as their score is O.

So we will add a where condition to the select query and the result of that would be as below.

b) With the help of IN BOOLEAN.

With IN BOOLEAN MODE we can exclude particular words. As shown in the below example. We are looking for results that have the word "Always" but not the word "-afraid".
So, we get the result as follows.

With IN BOOLEAN MODE, we can also specify the maximum distance between the terms we are searching for.

Remember our example of "watches and shoes" the distance measured here will be 3.

So, we have to add @distance (i.e the maximum number of words or the distance value)

We are looking for a record that has "gods devise" and the distance between them is 4.

Here's one more example. It will help you understand it better.

Conclusion:

One of the reasons that people tend to choose FTS over 'like' is because of the mysql full text search performance.

These are just some of the basic things that can be done with FTS.

There's so much more to it. You can always go to the MySql official website to get more details.

I am an avid book reader, who enjoys technology as well as writing.

Comments