title: Efficient lookup in MySQL table with millions of rows
body: <p>I have a CSV file with about 20 million rows that I'd like to use in my web application. The data is a mapping of postal/zip codes to actual street addresses in the following format:</p>
  
  <pre><code>[zip_or_postal_code] [street_number] [street_name] [city] [state_or_province] [country]
  </code></pre>
  
  <p>My goal is to keep my lookups (searching by zip/postal code) under 200ms.</p>
  
  <p>I'm not sure if this would make a difference, but I was planning on doing the following:</p>
  
  <ul>
  <li>Move the <code>state/province</code>, <code>country</code>, and <code>city</code> columns to their own tables and reference those in my primary table in order to avoid unnecessary bloat.</li>
  <li>Some zip/postal codes cover multiple streets and addresses, so I will consolidate the data and have 1 zip/postal code and will store multiple addresses in something like a varchar. This should cut down a few million rows from the table.</li>
  </ul>
  
  <p>What are some optimizations I could make to help with lookup speed? As an example, Google's reverse geolocation API returns a result in under 300 ms with HTTP overhead included. How do they do it?</p>
  
  <p>Also, I am open to using other databases, but since I'm already using MySQL, that would be preferable.</p>
  
  <p><strong>Edit</strong>: The lookups will <em>always</em> be done by zip/postal code, so as an example: given the zip 12345 I'd need to return the street #(s)/name(s), city, state, and country. The street #(s)/name(s) will be stored as a single string field, however, so my app will take care of parsing them.</p>