Index creation in the table

Question

I have a table that has 140 fields. From this table 45 items are important in a query. Every time a user selects an item as true, my script mounts the SQL as requested. In this case, should I create 45 indexes in the table or not? The data is now char(1) which can be S or N. But I can switch to int type 1 or O. What do you suggest to me? The database is Mysql.

Answer 1

I suggest normalizing this table and in the tables that appear create the indexes only for the fields that will be used as a condition in the queries.

Answer 2

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.