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 with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.

create table t2 
select field_1,
       sum(ifnull(field_2,0)) as field_2, 
       sum(ifnull(field_3,0)) as field_3,
       sum(ifnull(field_4,0)) as field_4 
from t1 
group by field1;

The data type of all the columns is varchar(200).

This query is not able to produce the result even after running for more than 1 day. Any suggestions on getting the results quickly will be helpful.

share|improve this question

closed as too localized by ypercube, RolandoMySQLDBA, StanleyJohns, dezso, Mark Storey-Smith Jun 15 '13 at 2:07

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do you really name your tables and columns like this (field_1, table2, ...)? –  ypercube Apr 14 '13 at 16:25
2  
Which columns are varchar(200)? The source columns or the target columns? GROUP BY on 10 million rows will probably take a while. –  Mike Sherrill 'Cat Recall' Apr 14 '13 at 17:04
1  
Not to mention the conversions of 3x10M values from varchar to numbers so the SUMs are performed. –  ypercube Jun 13 '13 at 20:30

1 Answer 1

Here's what I'd do:

  1. Use EXPLAIN on your SELECT to reveal which indexes it's using.
  2. Tune your query and repeat step 1 until satisfied.
  3. Create your new table with the desired structure.
  4. Use INSERT ... INTO new table SELECT FROM ... old table replacing the select part with the SELECT query you tuned in step 2.
share|improve this answer

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