title: Should I choose the fulltext index or locate() position() function?
body: <p>I have a problem of my sql performance after I received a slow sql warning for 2 weeks.</p>
  <p>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: &quot;（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02,（抖音）澳贝母婴旗舰店21/02&quot; or &quot;广东荔枝红文化传媒有限公司,（抖音）澳贝母婴旗舰店21/02&quot;.</p>
  <p>Maybe I would split this column into a new relational table. It's in a later discussion.</p>
  <p>the size of this querying table is around 300k. Data length is about 300MB and index length is 37MB.</p>
  <p>the code below searches through the fullText index while executing while I do the explain. It runs for 4s which is a little unacceptable.</p>
  <pre><code>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 (&quot;*抖音*&quot; IN boolean MODE ) 
  ORDER BY
      create_time DESC 
      LIMIT 1200;
  </code></pre>
  <p>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.</p>
  <p>[for sql1] <a href="https://i.imgur.com/tewWzDG.png" rel="nofollow noreferrer">https://i.imgur.com/tewWzDG.png</a>
  [for sql1] <a href="https://i.imgur.com/iCHjjHW.png" rel="nofollow noreferrer">https://i.imgur.com/iCHjjHW.png</a></p>
  <p>the other sql below replacing the &quot;match against&quot; into position() or locate() even like function gets a higher performance. It runs for 1.1s.</p>
  <pre><code>SELECT
      * 
  FROM
      customer c 
  WHERE
      receiver_mobile_decrypted IS NOT NULL 
      AND c.decrypted_flag = 1 
      AND c.updated_flag = 1 
      and position(&quot;抖音&quot; in c.shop_name) &gt;0
  ORDER BY
      create_time DESC 
      LIMIT 1200;
  </code></pre>
  <p>I would like to provide you the details of this execution.
  [for sql2]<a href="https://i.imgur.com/mAfGiio.png" rel="nofollow noreferrer">https://i.imgur.com/mAfGiio.png</a>
  [for sql2]<a href="https://i.imgur.com/nqEpM3a.png" rel="nofollow noreferrer">https://i.imgur.com/nqEpM3a.png</a></p>
  <p>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.</p>
  <h1>UPDATE</h1>
  <p>I found some not clarified questions when I post this problem.</p>
  <p>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&quot;%%&quot; function.</p>
  <p>I set the ngram_token_size to 2. like the picture below</p>
  <p><a href="https://imgur.com/UDRqhno" rel="nofollow noreferrer">https://imgur.com/UDRqhno</a>
  <a href="https://imgur.com/BH3dByt" rel="nofollow noreferrer">https://imgur.com/BH3dByt</a></p>