Can some explain me, my mysql match against why not working

Question

i am use mysql full text on link column

but some time text match and some time its not working

i have example

http://sqlfiddle.com/#!9/470eb28/6

CREATE TABLE domains
    (`name` varchar(300))
 ENGINE=InnoDB;
    
INSERT INTO domains
    (`name`)
VALUES
    ('https://www.google.com'),
    ('https://www.yahoo.com'),
    ('https://www.instagram.com/painfreestrength/')
;

CREATE FULLTEXT INDEX idx_1 ON domains (`name`);

query

select * from domains where match(name) against('"painfrees"')

Answer 1

Match against will require you to use whole word

select * from domains where MATCH(`name`) AGAINST('wholeword') 

i.e. for your case

select * 
from domains where MATCH(`name`) AGAINST('"https://www.instagram.com/painfreestrength/"')

select * 
from domains where MATCH(`name`) AGAINST('"painfreestrength"')

select * 
from domains where MATCH(`name`) AGAINST('"instagram"')

But to if you must use painfrees

use like or regex

select * from domains where name like "%painfrees%"

select * from domains where REGEXP_LIKE(name, 'painfrees')

Answer 2

Try This

select * from domains where name like '%painfrees%';

Answer 3

There is no way you could make it more efficient in terms of performance against a huge database, especially when you are ought to use the LIKE pattern, assuming the table structure is exactly as you put in your question, assuming you already have the column INDEXed.

A viable approach would be to keep the database optimized in alphabetical order with the 'Name' column and LIMIT your search for N rows, this will greatly improve your query.

Another thing you could do is to extract the root domain and keep it in another column, that will improve the performance.