I have a problem of my sql performance after I received a slow sql warning for 2 weeks.
Firstly I need to mention that there are so many redundancy in the shop_name column. Because it currently records all the history of customers' transaction. This is what it looks like: "（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02" or "广东荔枝红文化传媒有限公司,（抖音）澳贝母婴旗舰店21/02".
Maybe I would split this column into a new relational table. It's in a later discussion.
the size of this querying table is around 300k. Data length is about 300MB and index length is 37MB.
the code below searches through the fullText index while executing while I do the explain. It runs for 4s which is a little unacceptable.
SELECT
*
FROM
customer c
WHERE
receiver_mobile_decrypted IS NOT NULL
AND c.decrypted_flag = 1
AND c.updated_flag = 1
AND MATCH ( c.shop_name ) against ("*抖音*" IN boolean MODE )
ORDER BY
create_time DESC
LIMIT 1200;
the image below is the explaination of the sql executing. The weird thing here is that I don't find the fulltext initialization in my latest navicat client. But I saw this step cost about 99.455% time of the whole in the production env. In the prod env, a navicat runs in an old version. You know, I cannot screenshot the detail in the prod env. This is what I can provide.
[for sql1] https://i.stack.imgur.com/l25yQ.png [for sql1] https://i.stack.imgur.com/G9aOL.png
the other sql below replacing the "match against" into position() or locate() even like function gets a higher performance. It runs for 1.1s.
SELECT
*
FROM
customer c
WHERE
receiver_mobile_decrypted IS NOT NULL
AND c.decrypted_flag = 1
AND c.updated_flag = 1
and position("抖音" in c.shop_name) >0
ORDER BY
create_time DESC
LIMIT 1200;
I would like to provide you the details of this execution. [for sql2]https://i.stack.imgur.com/1rsFZ.png [for sql2]https://i.stack.imgur.com/LFLXd.png
Under current situation, I modified my sql into the 2nd which is acceptable now. But if you can provide me any hint or suggestion, I would appreciate that.
UPDATE
I found some not clarified questions when I post this problem.
I did really set the ngram to this fulltext index. Because if I didn't do so, the querying results would be different from the orginal like"%%" function.
I set the ngram_token_size to 2. like the picture below
https://i.stack.imgur.com/go6ts.jpg https://i.stack.imgur.com/tqBNF.jpg
