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.
CREATE TABLE
statement you have used to create the table and some sample queries?