MySQL 'Too many keys' error

Question

I'm creating a MySQL table in Rails which has about 170 boolean fields, each of which needs to be searchable and hence indexed, but when I create the indexes I get the error message:

To many keys specified; max 64 keys allowed

Is this limit hard coded or is there a config switch I can flip which wold get around it?

Or do I need to refactor the table? The obvious way to me looks to be to join multiple tables together and run a query such as

Table.find.all(:conditions => "join1.fieldx = true and join2.fieldy = true")

Are there any performance or other gotchas with such a strategy?

Answer 1

Apparently the only way to do this at the moment is to increase the value of MAX_KEY in the source code and to recompile MySQL. (Source)

Or do I need to refactor the table?

Probably yes. In addition to the problem of many keys, indexing boolean columns is not particularly beneficial. B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique).

If you have to stick with this design, I think you may want to consider leaving the boolean columns non-indexed and without any foreign key constraints.

Answer 2

Classify the boolean fields into groups and maintain separate tables for those groups. Make joins in your query to retrieve results. Use the EXPLAIN EXTENDED SELECT to optimize the indexes for your queries.

Also, try to maintain covering indexes because MySQL uses only one index per table.

http://www.mysqlperformanceblog.com/2009/06/05/a-rule-of-thumb-for-choosing-column-order-in-indexes

EDIT 01:

As Daniel mentioned in his answer, the index cardianality for Boolean values is not going to help you any way. Sometime things get worse when you use index for such columns.

Instead of using 170 boolean columns, you can use 170 tables with reference to the primary key of the parent data.

Let's say that your parent table is students and the primary key is student_id.

Have separate tables for 170 subjects they undertake in their schooling age.

If a student successfully passes the English subject in his Form 1, insert the appropriate student_id in the form_1_english table. This way, you have only unique values in that column and an index on that column will be much more efficient.

Answer 3

Another alternative - store your booleans as bit flags in a number field.

e.g. instead of four fields "true, false, false, true" store one number "9" (1001 in binary).