title: Mysql substring query
body: <p>I have a table of region restriction for product codes. Product code doesn't have a fixed length, bud it could vary between 10 to 25 numbers. The restriction could contain a prefix of a product so, that all product from in that range will be forbidden.
  Used DB is MariaDB/Mysql and here is a table definition:</p>
  
  <pre><code>CREATE TABLE product_restrict (
      `id` VARCHAR(25) NOT NULL,
      `region` VARCHAR(3) NOT NULL,
      `from_dttm` DATETIME NOT NULL,
      `to_dttm` DATETIME NULL DEFAULT NULL,
      PRIMARY KEY (`region`, `id`, `from_dttm`))
  ENGINE = InnoDB;
  </code></pre>
  
  <p>At this moment, I use 15 queries defined by the length of the target product code, so I'm able to find all of the prefixes, so in my code, I have 15 queries like that:</p>
  
  <pre><code>SELECT * 
  FROM product_restrict
  WHERE
   region='XXX' AND
  (
      id = "9" OR 
      id = "98" OR 
      id = "987" OR 
      id = "9876" OR 
      id = "98765" OR 
      id = "987654" OR 
      id = "9876543" OR 
      id = "98765432" OR 
      id = "987654321" OR 
      id = "9876543210" OR 
      id = "98765432109" OR 
      id = "987654321098" OR 
      id = "9876543210987" OR 
      id = "98765432109876" OR 
      id = "987654321098765" OR 
      id = "9876543210987654" 
  ) AND (
   now() &gt;= from_dttm AND
   ( now() &lt; to_dttm OR to_dttm is null)
  );
  
  
  SELECT * 
  FROM product_restrict
  WHERE
   region='XXX' AND
  (
      id = "9" OR 
      id = "98" OR 
      id = "987" OR 
      id = "9876" OR 
      id = "98765" OR 
      id = "987654" OR 
      id = "9876543" OR 
      id = "98765432" OR 
      id = "987654321" OR 
      id = "9876543210" OR 
      id = "98765432109" OR 
      id = "987654321098" OR 
      id = "9876543210987" OR 
      id = "98765432109876" OR 
      id = "987654321098765" OR 
      id = "9876543210987654" OR 
      id = "98765432109876543" 
  ) AND (
   now() &gt;= from_dttm AND
   ( now() &lt; to_dttm OR to_dttm is null)
  );
  </code></pre>
  
  <p>In this table, is around 100 millions of records. My question is, is there a way to reduce this to a single query with the same select performance? Change of table structure is unfortunately out of my power.</p>
  
  <hr>
  
  <p><em>Edited after INSTR() hint from @Pham X. Bach:</em></p>
  
  <p>I did some tests on my local sample DB, where I have only 670 000 sample records, and the INSTR() is working, but from the performance perspective, it looks much worse. I'll have to wait till tomorrow, to do this test on the production sample.</p>
  
  <p>Here is analyze (explain) for my original query:</p>
  
  <pre><code>Id  select_type table               type    posible_keys    key     key_len ref     rows    r_rows      filtered    r_filtered  Extra
  1   SIMPLE      product_restrict    range   PRIMARY         PRIMARY 201             17      2.00        76.47       100.00      Using where
  </code></pre>
  
  <p>And here for the INSTR:</p>
  
  <pre><code>Id  select_type table               type    posible_keys    key     key_len ref     rows    r_rows      filtered    r_filtered  Extra
  1   SIMPLE      product_restrict    ref     PRIMARY         PRIMARY 98      const   335022  671732.00   100.00      0.00        Using where
  </code></pre>
  
  <p>INSTR query, for example:</p>
  
  <pre><code>SELECT * 
  FROM product_restrict
  WHERE
   region='XXX' AND
   INSTR('98765432109876543', id) = 1 AND (
   now() &gt;= from_dttm AND
   ( now() &lt; to_dttm OR to_dttm is null)
  );
  </code></pre>