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?
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. Jan 12, 2015 at 10:07

2 Answers 2

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

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. Jan 13, 2015 at 12:34
0

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

2
  • Thanks, again looking for speed improvement or progress bar as opposed to non blocking. 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.