Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you provide the CREATE TABLE statement you have used to create the table and some sample queries? –  ypercube Sep 8 '13 at 15:12
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks for the answer, I thought about it long time and now I think I'll start to do it :) –  Michael Arenzon Sep 8 '13 at 15:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.