1

My table has 981 million rows and is about 30GB large. It is six columns: ID, Ticker, Date, Time, Price, Volume. The data was in text files and using LOAD DATA INFILE was actually quite painless and only took a couple hours. However, each select statement takes 5 minutes now because of the number of rows.

I want to create an index on Ticker, Date, Time. I used the following command:

create index tickerdatetime on kibot (Ticker,Date,Time);

It is now been over eight hours and it is still working. My machine is ok (PC, 3.40GHz, quad core, 12GB RAM). Two questions:

  1. Is there a better (ie faster) way of doing this?
  2. Is there any way I can know what percentage is done? Am I 10% done or 80% done?
Improve this question
1
  • 1
    Ok, so just so happens that a few minutes after posting this, the query completed. It took 6hrs and 24mins (guess I thought wrong when I said over 8hrs). Now the same select that took 5mins is taking 0.04secs. However, I'm still curious regarding the answers to the two questions above if anyone can shed any light.
    – ashishsingal
    Jan 12, 2015 at 10:07

2 Answers 2

Reset to default
2

Yes, there is a safe way (not faster though) to alter schemas if you use tool. This does not require any downtime. The one we use everyday on our production is to use

pt-online-schema-change this tool will show you the progress of the SQL.

I hope this helps

Improve this answer
1
  • Thanks, I'd seen this before -- but non blocking wasn't really my issue here -- it was more speed. Guess indices require a lot of processing as the data size gets large.
    – ashishsingal
    Jan 13, 2015 at 12:34
0

YMMV - but Shlomi Noach has also written a tool for this purpose - it's available here.

Improve this answer
2
  • Thanks, again looking for speed improvement or progress bar as opposed to non blocking.
    – ashishsingal
    Jan 13, 2015 at 12:35
  • Well, then maybe you should go with the Percona tools.
    – Vérace
    Jan 13, 2015 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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