title: How can I index list data in SQL for rapid search on certain elements or groups of elements?
body: <p>I'm going to be storing pronunciations of words in a MySQL table and I'd like to have a way to quickly search for pronunciations that have certain properties.</p>
  
  <p>Here's an example of what some data might look like:</p>
  
  <ul>
  <li>plaintext word: "cornucopia"</li>
  <li>pronunciation: ["K", "AO2", "R", "N", "AH0", "K", "OW1", "P", "IY0", "AH0"]
  
  <ul>
  <li>If you're unfamiliar with pronunciation, the <a href="http://www.speech.cs.cmu.edu/cgi-bin/cmudict?in=cornucopia&amp;stress=-s" rel="nofollow noreferrer">CMU Pronouncing Dictionary</a> has a good explanation and was how I generated this example.</li>
  </ul></li>
  </ul>
  
  <p>The simplest approach to storing the list data that I could think of was to just dump the list as a json-encoded string.  The problem is that that approach doesn't seem to allow for a useful index on that column.</p>
  
  <p>Here are some example queries I'd like to be able to run very quickly (with the relevant data indexed in my database):</p>
  
  <ul>
  <li>Find all pronunciations that have this sequence of vowels: ...</li>
  <li>Find all pronunciations that have consonant(s) XXX in position(s) YYY.</li>
  <li>Find all pronunciations that have vowel XXX in the <em>last</em> position.</li>
  <li>Find all pronunciations that have this sequence of lexical stress somewhere in them: [0, 2, 1]</li>
  </ul>
  
  <p>One idea I've had is to separate out the lexical stress information (the numbers to the right of each vowel phoneme in the example above) and store that stress information separately (e.g. [2, 0, 1, 0, 0]), but even then I'm not sure how I could index it.</p>
  
  <p>Another idea that would seem to at least solve the lexical stress queries might be to store every possible lexical stress pattern in a separate table, and then have each pronunciation simply refer (via a foreign key) to one of those lexical stress patterns.  I could then run my (Python) server code against that smaller set of possible lexical stress patterns to determine which patterns were a match, and then query the pronunciation table for those pronunciations which had one of those stress patterns.  But I'm curious to know if there's some way to create a faster pure-SQL solution.</p>
  
  <p>But to be clear: the main issue I'm interested in is indexing and querying the lists of phonemes.</p>