I have a movie site which has a database of 300000 rows where each row contain about 30 columns.
I am makeing a search function using PHP but I found that it took about 30 seconds to load a search result. Its obviously not acceptable for most of the visitors. That’s why I am trying to build a FULLTEXT index after doing some research on the internet.

First, let me give some background information of the hosting environment:

• VPS 1Gb 1CPU
• Nginx 1.19.4
• MySQL 5.5.62
• PHP-7.2
• table is MyISAM

There are 30 columns for each row. To build the searching function, I only match 2 of the columns (vod_name, vod_en) and then print all 30 columns with HTML

        <table>
            <tr>
                <td>{$row['vod_name']}<td>
                <td>{$row['vod_en']}</td>
                <td>{$row['vod_actor']}</td>
                <td>{$row['vod_year']}</td>
                <td>{$row['vod_pic']}</td>
                ...
                ...
            </tr>
        </table>

Among 300000 data, there are 800 rows start with PPPD and ABP. Here are some examples of the rows:

>Video 1
>
>vod_name: PPPD-862 AAAAA    
>vod_en: PPPD-862
> 
>Video 2
>
>vod_name: PPPD-875 BBBBB        
>vod_en: PPPD-875
> 
>Video 3
>
>vod_name: PPPD-879 CCCCC    
>vod_en: PPPD-879
> 
>Video 4
>
>vod_name: ABP-944 DDDDD     
>vod_en: ABP-944
> 
>Video 5
>
>vod_name: ABP-998 EEEEE     
>vod_en: ABP-998

First search query I tried :

(assume visitor enter keyword: PPPD)
$sql = "SELECT * FROM table_name WHERE vod_name LIKE '%PPPD%'";

This took about 30s

(assume keyword: ABP)
$sql = "SELECT * FROM table_name WHERE vod_name LIKE '%ABP%'";

This also took about 30s

Then I built a FULLTEXT index in phpmyadmin.

Then I try to switch to use match against:

(assume visitor enter keyword: PPPD)
$sql = "SELECT * FROM table_name WHERE MATCH (vod_name,vod_en) AGAINST ('PPPD')";

This time the search result loads in 8s. But I tested the same syntax in phpmyadmin. It just took 0.0032s to finish the search.

(assume visitor enter keyword: ABP)
$sql = "SELECT * FROM table_name WHERE MATCH (vod_name,vod_en) AGAINST ('ABP')";

Weird things happend. It shows 0 result!!.

To have a better understanding of my condition. I have made a testing page. You can try different search method on the page (only text):
Click me to go to the test php page

My questions are:

1. How to use the FULLTEXT index correctly to match the rows in above situation?
2. How to load the result within 2 sec?

Expected outcome:

1. Searching "ABP" using MATCH.. AGAINST.. will shows video 4 and vidoe 5
2. The correct results show in 2 sec

I could see a similar movie website with an even larger database. They show the result instantly. Hopefully someone could help me. I have searching for the answer for few weeks. Thank you guys to read such a long paragraph. I am a newbie.

Source: Ask PHP