MySQL Forums
Forum List  »  Optimizer & Parser

best way to optimize this query
Posted by: Jap bn
Date: July 14, 2012 09:54AM

I got a simple query as follows:

Select aaa, bbb, SUM(ccc), SUM(ddd), eee, fff, ggg, hhh
From table1
Group by aaa, bbb, eee, fff, ggg, hhh;

I have 150millions records in csv format. I was trying to use a index (aaa,bbb,eee,fff,ggg,hhh) in table1 but it turned out to be very slow when importing the data to the table1 using load data. after 20hrs, only 3millions records were inserted.

Then i reduced the index to only single column, index(aaa). But the explain command told me it is not possible to use index a full table scan is needed.

+----+-------------+-------+------+---------------+------+---------+------+-----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----------+---------------------------------+
| 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 150000000 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----------+---------------------------------+

What should i do to reduce the time of the query? I know that if i'm not using indexing, it will even take more time to run the query.

Please kindly comment.

Options: ReplyQuote


Subject
Views
Written By
Posted
best way to optimize this query
2828
July 14, 2012 09:54AM
1623
July 15, 2012 10:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.