title: MySQL: Select longest string that occurs in search string
body: <p>I have a table like this:</p>
  
  <pre><code>id    path
  1     /
  2     /city/
  3     /city/europe/
  4     /city/north america/
  6     /city/europe/germany/berlin/
  7     /city/europe/germany/
  8     /city/north america/usa/
  [...]
  </code></pre>
  
  <p>and a search string like this:</p>
  
  <pre><code>"/city/north america/usa/florida/miami/"
  </code></pre>
  
  <p><br>
  I need to select the longest path thats inside the search string so the result would be </p>
  
  <pre><code>id   path
  8    /city/north america/usa/
  </code></pre>
  
  <p><br>
  Every parent to any path in the table exists. The paths can be arbitrarily long. The search string contains at least "/".</p>
  
  <p>I can think of a few solutions like using wildcards on the column, splitting the search string and performing a select with IN or joins.</p>
  
  <p><b>What is the best and fastest way to do this?</b>
  <br>
  The table can be modified in any way.</p>
  
  <p><br>
  EDIT:
  The fastest solution with which I have come up so far:</p>
  
  <pre><code>SELECT * FROM table_name
  WHERE path IN ('/','/city/','/city/north america/', '/city/north  america/usa/', '/city/north america/usa/florida/', '/city/north america/usa/florida/miami/')
  ORDER by LENGTH(path) DESC LIMIT 1;
  </code></pre>