How to use like query on FullText Index Column

Question

I have this user table structure.

    `users` (
      `id` bigint(20),
      `balance` bigint(20),
      `current_parent` bigint(20),
      `parents` varchar(45),
      `role_id` int
    );

There are multiple role hierarchy. I have save all upline role id's comma separated in parents column.

This is my table sample data.

Example 1: Let's say if want to fetch sum of balance of children of parent id 1.

Using FIND_IN_SET.

select sum(balance) from users where FIND_IN_SET(1, parents);

Using LIKE

SELECT sum(balance) FROM `users`
    WHERE parents like '1,%'
       or parents like '%,1,%'
       or parents = 1;

Example 2: If the parent id is 2.

Using FIND_IN_SET.

select sum(balance) from users where FIND_IN_SET(2, parents);

Using LIKE

SELECT sum(balance) FROM `users`
    WHERE parents like '2,%'
       or parents like '%,2,%'
       or parents = 2;

But above query is taking too much time to execute. Currently user table has 1.5 million records. So I have found solution FULLTEXT search . My question is how can I achieve above query using FullText? or any other solution to improve my query performance.

Answer 1

Note the order of arguments and quoting:

WHERE FIND_IN_SET('1', parents)

Simpler to write, and probably runs 3 times as fast. But it does require checking each row.