I am came across this excellent solution about the ADD INDEX method to query. This is perfect for my case where my table is having 280 million rows with data belonging to many sensors from the field. I am selecting only one sensor data. I came across the ALTER TABLE mysql_table ADD INDEX index_name (mysql_column); I am ready to experiment this. But I am terrified whether this is going to change the data stored on the MySql server anyway permanently?

CodePudding user response：

First, if you have doubts, then you shouldn't do this on important data until you have practiced on some test data and grown more confident in your understanding of how the feature works.

This is in fact good advice for any software feature! If you're not confident with any tool or feature, then don't use it for the first time on important files that are hard to reproduce or recover.

Adding an index does store the index data structure in the file stored for your table, so it does modify the tablespace persistently. But it doesn't change your data.

The index is basically a copy of the values in the column(s) for which you define the index, and each entry in the index also has a link back to the primary key of the row(s) where the respective value occurs.

You can think of an index on a database table like an index at the back of a book. It's a pre-sorted list of individual values, each one is followed by a list of the page numbers where that value is found. Creating the index in a book does not change the text on the pages referenced by the index. It only makes it easier to find where they occur.