Creation of index in table

1

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

    
asked by anonymous 08.05.2015 / 22:03

2 answers

0

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.

    
09.05.2015 / 16:06
1

I would like to normalize this table and in the tables that appear create the indexes only for the fields that will be used as condition in the queries.

    
08.05.2015 / 22:42