Your question only arises because the table has 45 columns that can be used to limit the query.
If the problem is these columns let's get rid of them.
If the 45 columns can only have two values: "V" | "F" or 0 | 1, why not treat them as bits ?
Following this approach the 45 columns can be transformed into just one.
Each one is now represented as a bit in a BIGINT UNSIGNED column.
I do not know what language the application uses that will handle the query data, the following code is in C # but can easily be translated into any language that allows you to do logical operations.
The 45 columns will only exist in the program and will be represented by as many variables as bool
.
To facilitate the explanation, each of these variables will have the name of 'columnX' where X bit position assigned to it in BIGINT :
bool coluna0;
bool coluna1;
bool coluna2;
...
...
..
bool coluna44;
Let's define two auxiliary methods for manipulating bits.
1 - Returns the value passed with bit ON at the position indicated by pos
private ulong TurnBitOn(ulong value, int pos)
{
return (value | pos);
}
2 - Returns true
if position pos
in value
is ON
private bool IsBitOn(ulong value, int pos)
{
return (value & (1 << pos)) != 0;
}
These two methods will serve to:
1 - Calculate the value to write to the table according to the selected options:
private ulong GetOptions()
{
ulong options = 0UL;
if(coluna0)TurnBitOn(options,0);
if(coluna1)TurnBitOn(options,1);
if(coluna2)TurnBitOn(options,2);
...
...
if(coluna44)TurnBitOn(options,44);
return options;
}
2 - "Setar" to variables according to the value in the table:
private SetOptions(ulong options)
{
coluna0 = IsBitOn(options, 0);
coluna1 = IsBitOn(options, 1);
coluna2 = IsBitOn(options, 2);
....
....
coluna44 = IsBitOn(options, 44);
}
With this approach queries are made only on a column and, if necessary, only one index is created.
Notes:
The GetOptions()
and SetOptions(ulong flags)
methods are just one example implementation.
If you can not use 64-bit integer values, divide the 45 columns into two groups and associate each group with a column in the table.