MySQL: Add index to an active table

Question

My _sitecustomers table has around 67,000 records and after optimizing the site's search I decided I should have indexed 1 or 2 more columns to speed up the query. Is there a way to index an already active table with this many records?

Yes, you can add the index to a table in use just as you would if the table was not in use. With 87,000 rows, it may take a little time depending on what is contained within the column you're indexing. — Nick Coons
– Nick Coons, Commented Aug 4, 2013 at 23:43
You should've made that a comment so I can mark it as the answer. :) — enchance
– enchance, Commented Aug 5, 2013 at 0:05

hjpotter92 · Accepted Answer · 2013-08-04 23:43:18Z

3

67k is not a real big number of records. A simple ALTER TABLE statement will work:

ALTER TABLE `_sitecustomers`
    ADD INDEX `col1` (`col1`),
    ADD INDEX `col2` (`col2`);

answered Aug 4, 2013 at 23:43

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

enchance Over a year ago

Correct me if this is wrong: ALTER TABLE _sitecustomers ADD INDEX somename (col1 ASC, col2 ASC). I'm trying to use 2 columns to make an index. Currently, one of my columns (e.g. col1) already has an index. Do I have to delete that first?

hjpotter92 Over a year ago

@enchance No. Multiple indexes can exist.

Collectives™ on Stack Overflow

MySQL: Add index to an active table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related