I would like to know some hints about how to index some columns of a MySQL table that has about 28 million rows.

Maybe I could recreate the table indexing it properly, then import these rows?

Could someone help me with this please.

  • You don't need to recreate table. Just use CREATE INDEX and it will index it. – Barmar Apr 11 '14 at 1:30
  • i know but are 28 millions of rows – user3466310 Apr 11 '14 at 1:34
  • it will take so long time, isn't? – user3466310 Apr 11 '14 at 1:34
  • 1
    It will take just as long if you recreate the table. – Barmar Apr 11 '14 at 1:40
  • I guess you're saying that you'll create a new table and copy the data from the old table, so you don't interfere with the old table, and then rename them. – Barmar Apr 11 '14 at 1:41

If you just wish to create a single index, CREATE INDEX would be the way to go.

However, if you wish to setup your table with multiple indices, setting up a new empty table and then copying from your old to your new table is IMO a more practical solution, since you can get everything ready and then simply run the insert when it's convenient.


The only way to create an index is to use CREATE INDEX statement. There are only 2 ways to apply this statement: 1. Empty table and copy data into it 2. To use it on populated table.

In Case 2 you need to consider availabe DB space, temp space, log space as well as memory and CPU time. If this table is large for your hardware, then there is a high chance that you'll run out of space before create index operation completes.

However, I would suggest to look at the way you are managing this table and apply techniques like partitioning or MERGEd tables. it looks like the better option for you will be to move data into a new table. This gives you an opportunity to do more to make your data more manageble.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy