Mysql – Do id columns help (in speed) select statements

group byMySQLoptimization

I have a table which contains data from aggregation software.
The columns mostly are int columns, but three of them are string columns.
It looks sort of like:

userId, someData1, someData2,     someData3, otherIdColumn, childId, websiteId
------------------------------------------------------------------------------

1, somestring,  someotherstring  justastring    3,             2,       1
1, somestring1, someotherstring1 justastring    3,             3,       3
1, somestring2, someotherstring2 justastring    2,             2,       1
1, somestring3, someotherstring3 justastring    1,             9,       5
2, somestring4, someotherstring4 justastring    4,             2,      10

and so on.

The table contains something like 80m rows for now.
When I'm trying to make a query using aggregation functions (SUM) and grouping by one of the string columns (someData1, someData2, someData3) it takes very VERY long time (more than 10 minutes for a query).

I'm trying to optimize the table right now, the first thing that I've did I've added indexes to the string columns, but I want to make it even faster.
I thought of adding an ID column (pk, ai, nn) as of it will be make the select queries faster.

What do you think about it? do you have any advice how more I can optimize this table? Note: I have only like 5 columns that I'm using group by. 3 of them is string columns, and they are making the problem.

Best Answer

Since you mentioned your typical queries involve grouping by string fields, I assume you have many duplicate values in these columns (otherwise grouping makes no sense).

You can definitely improve performance by creating lookup table[s] for values stored in someData1, someData2, someData3 with integer primary key and changing original someData1 to someData1_id . You still need index on someData1_id, but taking into account number of rows , the new index will be much smaller than index on string field, and as a result , much faster...

Related Question