Your question only arises because the table has 45 columns that can be used to limit the query.
If the problem is these columns we’ll 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 only one.
Each one becomes represented as one bit in a column BIGINT UNSIGNED.
I don’t know which language you use in the application will handle the query data, the following code is in C# but it 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 other variables of type bool
.
To facilitate the explanation each of these variables will have the name of 'columnX' where X represents the position of bit assigned to it in BIGINT:
bool coluna0;
bool coluna1;
bool coluna2;
...
...
..
bool coluna44;
Let’s define two auxiliary methods to manipulate the bits.
1 - Returns the value passed with the bit ON in the position indicated by pos
private ulong TurnBitOn(ulong value, int pos)
{
return (value | pos);
}
2 - Returns true
if the 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" the 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 consultations are made only on one column and, if necessary, on the creation of only one index.
Notes:
The methods GetOptions()
and SetOptions(ulong flags)
are just an example of implementation.
If 64-bit integer values cannot be used, divide the 45 columns into two groups and associate each group to a column in the table.
140 fields? It won’t be too many fields for a single table?
– ramaral
I can divide but I will need these 45 fields in a table. All fields are query
– André
How many records are in the table?
– ramaral
I’m still doing the modeling and doing some performance tests and I kept thinking about these fields that I will always consult, but the problem is the amount of indexes. The table can have N records.
– André
If the table has few records (in the order of hundreds) little gains in having indexes. My approach is to always start without indexes. Only when I start to notice some slowness and I create them and always check if it improves. See this reply where you have some considerations about that.
– ramaral
Which best in performance. A var(1) or int field for an index?
– André