title: Can my query with multiple LIKE statements and REGEXP be more efficient?
body: <p>I'm constructing a dynamic query to select dropped domain names from my database. At the moment there are a dozen rows but I'm going to get data soon which will have records of up to 500,000 rows.</p>
  
  <p>The schema is just one table containing 4 columns:</p>
  
  <pre><code>CREATE TABLE `DroppedDomains` (
    `domainID` int(11) NOT NULL AUTO_INCREMENT,
    `DomainName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `DropDate` date DEFAULT NULL,
    `TLD` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`domainID`)
  ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>I did not create the schema, this is the live database schema. Here's sample data:</p>
  
  <p><img src="https://i.stack.imgur.com/omWao.png" alt="enter image description here"></p>
  
  <p>I've constructed probably the most complex type of query below. The criteria is as follows:</p>
  
  <p>SELECT any number of domains which</p>
  
  <blockquote>
    <ol>
    <li>Start with the word 'starts'</li>
    <li>End with the word 'ends'</li>
    <li>Contain the word 'containsThis' anywhere in the domain name</li>
    <li>Contain the word 'ContainsThisToo' anywhere in the domain name</li>
    <li>Include at least one digit</li>
    <li>The domain name must be at least 49 characters. Multibytes need to count
    as one character( I used CHAR_LENGTH
    ).</li>
    <li>The domain name must be at least under 65 characters. </li>
    <li>The TLD must be 'org'</li>
    <li>The DropDate needs to be later than <code>2009-11-01</code></li>
    </ol>
  </blockquote>
  
  <p>Here's my query so far:</p>
  
  <pre><code>SELECT
  *
  FROM
  DroppedDomains
  
  WHERE
  
  1=1
  
  AND DomainName LIKE 'starts%ends'
  AND DomainName LIKE '%containsThis%'
  AND DomainName LIKE '%containsThisToo%'
  AND DomainName LIKE '%-%'
  AND DomainName REGEXP '[0-9]'
  AND CHAR_LENGTH(DomainName) &gt; 49
  AND CHAR_LENGTH(DomainName) &lt; 65
  AND TLD = 'org'
  AND DropDate &gt; '2009-11-01'
  </code></pre>
  
  <p>Here are my questions</p>
  
  <ol>
  <li><p>Would it extremely benefit the performance considering I'll have half a million rows, if I made the <code>TLD</code> column its own table and just make the <code>TLD</code> column a foreign key to that? There will only be 5 TLDs ( com, net, org, info, biz ). I realize there are more TLDs in the real world, but this application will only have 5. The user cannot specify their own TLD.</p></li>
  <li><p>I know that <code>REGEXP</code> and 500,000 rows is probably a recipe for disaster. Is there anyway I can avoid the <code>REGEXP</code>? </p></li>
  <li><p>Are there any other optimizations to the query I can do? Like merge <code>LIKE</code>s or use other functions such as maybe <code>INSTR</code>? And should I implement any specific sort of caching mechanism?</p></li>
  </ol>