How to effectively search through MySQL DB with multiple searcheable columns?

Question

I have a table with 60 boolean (TINYINT(1)) searcheable columns. User has possibility of using any subset of the given columns as search condition. Based on that I cannot create a good index for my needs. I was wondering if I can create another column (concat_col) of type BIT(60) that would be concatenation of the searchable columns, i.e.

Table_A:
id   |col1|col2|...|col60|concat_col
9999 | 1  | 0  |...|  1  |10...1

I could then create a good index for it (on concat_col) but there is one problem - how do I create a query for it?

Please see this example written in pseudo code:

Standard version (This would obviously work fine):

SQL = SELECT * FROM Table_A WHERE col1=1 AND col60=1 

My version ('*' is wildcard because it is not '1' neither '0'):

SQL = SELECT * FROM Table_a WHERE concat_col = '1*...1'    

Is there any possibility of solving this problem effectively? Thank you very much for your help!

Answer 1

try with:

SQL = SELECT * FROM Table_a WHERE concat_col REGEXP '^1[0-9a-zA-Z]{58}1$'