Index creation in the table

Asked

Viewed 92 times

1

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.

  • 140 fields? It won’t be too many fields for a single table?

  • I can divide but I will need these 45 fields in a table. All fields are query

  • How many records are in the table?

  • 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.

  • 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.

  • Which best in performance. A var(1) or int field for an index?

Show 1 more comment

2 answers

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.

  • The table is already normalized. What can I do and split to another table with these key fields. But even then the client will have the possibility to define which items you want to search. He can define only 1 as well as the 45.

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 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.

  • Interesting method. I was thinking of something to bring everyone together. I will try to implement. I only have a doubt, the position will look like 01011100 ...etc. In the table I will always record the 45 positions or only the selected ones. And what about the SQL search? I’ll have to search for the results with other conditions and save them to memory. Then apply the concept in a loop and filter. This?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.