Creating Compound Indexes on Many Flags So, according to this article, low-cardinality indexes may negatively impact performance:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

Let's say you have 15-20 yes/no flags you want to be able to search on in any combination, with a kicker of having the final column of the index be your SORT BY column of high-cardinality values. Let's say we have 5 or so different columns we want to be able to sort on. We want our queries to always use an index without having to do any extra table scans or use temporary tables or do any extra sorting.

To create all of these indexes, it would require a permutation of all of these combinations - you're gonna have a lot of indexes to update. And it's possible it's going to be very inefficient if I understand the above article correctly.

Is it possible to consolidate all of your flags into one column using some kind of bitmasking technique? I'm vaguely familiar with the concept but pretty clueless when it comes to the execution.

P.S. If it makes a difference, I'm using MySQL.
Bottom line:  Can't be optimized.

But, you can speed things up some by minimizing the size of the rows.  Such will be especially beneficial if the table is too big to be cached.

Here's one approach.

Put the flags in a single MEDIUMINT UNSIGNED column.  That will give you up to 24 yes/no values in a mere 3 bytes.  DATE is 3 bytes.  Your id is perhaps 4 bytes.  Put those three things into an index -- total of 10 bytes (plus a lot of overhead).

I suggest this ordering:  INDEX(date, flags, id).  Then have code like:

SELECT id FROM tbl
    WHERE date &gt;= ... AND date &lt; ...
      AND (flags &amp; $mask) = $value;


The steps will be


Range scan using the first column of the index (date) to limit the number of index 'rows' to search.
Perform the boolean operation to filter out rows based on the flags
Get the id so you can go off to the table to get whatever other info you need.


Something like:

SELECT b.*
    FROM (
        SELECT ... -- as above
         ) AS filter
    JOIN tbl AS b  USING(id)
    ORDER BY ...


The ORDER BY on the end won't be able to use any index, but it will benefit from having all the filtering already done, so only the desired number of rows will be involved.