title: Optimizing SELECT through indexes
body: <p>I have a large table of about 7M rows (and counting). I'm trying to optimize a SELECT to be as fast as possible. SELECT is from a single table, no joins. Database is IBM Informix. SELECT speed is priority, however there is a pretty constant number of inserts to the table.</p>
  
  <p>There are 23 parameters in WHERE, but I've identified the dates and the language as the ones that are worth indexing:</p>
  
  <pre><code>...
  WHERE (? &gt;= valid_from OR valid_from IS NULL)
  AND (? &lt;= valid_to OR valid_to IS NULL)
  AND ((? &gt;= date_from AND ? &lt;= date_to) OR (? &gt;= date_from AND ? &lt;= date_to)) 
  AND language = ?
  ...
  </code></pre>
  
  <p>Now, what would be the best way to create indexes on that?</p>
  
  <ol>
  <li><p>Do I create separate indexes from valid_from, valid_to, date_from, date_to and language? Or do I create three composite indexes (valid_from, valid_to), (date_from, date_to) and language? Or do I create one large composite index with all five? All fields are mandatory.</p></li>
  <li><p>Are indexes on dates that are being compared a good idea, or should I restrict myself only to those fields that use = (exact match)?</p></li>
  <li><p>If the composite indexes are the way, I guess the order of the fields in composite index is important - how do I order columns in index? Columns date_from and date_to would give me the biggest first reduction, but language might give me the faster reduction (though this is assumption I pulled out of thin air - see question 2).</p></li>
  <li><p>If there are multiple indexes, does Informix use all of them or just one (and which one)?</p></li>
  <li><p>My tests show that order of conditions in WHERE is not important, however I might be wrong - is it?</p></li>
  <li><p>Some of the conditions in WHERE are on columns which are sets. Informix doesn't allow me to index those columns. Does that mean that those conditions are matched by sequential scan? Does it make sense speed-wise to move those to separate table and JOIN them?</p></li>
  </ol>
  
  <p>What I've done so far:</p>
  
  <ul>
  <li>Obviously, there are no joins and this is a denormalized table. I'm paying for this with a somewhat larger number of rows that are being inserted, which is fine with me since updates are not time sensitive.</li>
  <li>I'm using pretty large Informix extents to avoid fragmentation, and pagesize which gives the best results for my table.</li>
  </ul>