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:
- Is there a better (ie faster) way of doing this?
- Is there any way I can know what percentage is done? Am I 10% done or 80% done?
Best Answer
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