title: How to optimise DB model for fast LIKE query with wildcards
body: <p>I would like to know what the most efficient way is to optimise a database model in SQL server for querying with LIKE with wildcards in the beginning. I'm not a DB expert, so all advice on use of indexes or other optimisations is welcome.</p>
  
  <p><strong>Situation:</strong>
  I have a table 'Product' with following columns:</p>
  
  <pre><code>ShortNameEN (varchar(50))
  ShortNameFR (varchar(50))
  ShortNameDE (varchar(50))
  ShortNameNL (varchar(50))
  LongNameEN (varchar(250))
  LongNameFR (varchar(250))
  LongNameDE (varchar(250))
  LongNameNL (varchar(250))
  </code></pre>
  
  <p>This table contains 300000+ records.</p>
  
  <p>I need to write a select statement to find records that <strong>contain</strong> a searchstring (only in ShortNameEN).
  My query is </p>
  
  <pre><code>SELECT * 
  FROM Products 
  WHERE ShortNameEN LIKE '%searchstring%'
  </code></pre>
  
  <p>Of course, this query is terribly slow. Adding indexes on ShortNameEN will not help, since they won't be used due to the first wildcard.</p>
  
  <p><strong>Question 1:</strong> Would it make sense to separate the ShortNameEN column from the rest of the table? I have no idea about disk access/row sizes/page sizes and how this would affect performance. Maybe there are other file-system related optimisations that could improve performance here?</p>
  
  <p><strong>Temporary solution</strong></p>
  
  <p>I found a creative 'trigram' solution, but with quite some impact on my model. For this I created a second table 'ProductNameFragments' that references my initial table, and breaks down every ShortNameEN, the following way:</p>
  
  <p>Example for ProductId = 123, ShortNameEN = 'PRINTER'</p>
  
  <pre><code>ProductId | NameFragment
  123       | PRINTER
  123       | RINTER
  123       | INTER
  123       | NTER
  123       | TER
  123       | ER
  123       | R
  </code></pre>
  
  <p>Trigger on the Product table will sync the ProductNameFragments table.</p>
  
  <p>This way I can join my two tables, and query without the initial wildcard.</p>
  
  <pre><code>SELECT p.* 
  FROM Product p, ProductNameFragment pnf
  WHERE p.Id = pnf.ProductId
  AND pnf.NameFragment LIKE '%searchstring%'
  </code></pre>
  
  <p>First tests show that this dramatically improves my search query performance.</p>
  
  <p><strong>Question 2:</strong> Should I use a regular index, or a clustered index on ProductNameFragment? And how will this affect performance when updating/deleting/inserting products?
  When updating one product name, this could potentially lead to 50 deletes and 50 inserts in the ProductNameFragments table. Can I force the index only to be updated once?</p>
  
  <p>In the end, I would prefer not to use the 'trigram' solution, because of the complexity. So any tips or tricks are more than welcome.</p>
  
  <p>Thx in advance</p>
  
  <p>Steven</p>