Should I add and delete index in one page?

Answer 1

Definitely not.

Building an index takes more time than scanning the entire table, so you will severely degrade your performance.

Just build the index once and leave it.

Answer 2

With "one time" index

O(n*log(n)) + O(n) + [cost of index delete - don't know this]

With index generated

O(n) - in ordering, but you have O(log(N)) additional time while INSERT and UPDATE exp column

---

O(n) is much faster than O(n*log(n)) + O(n) then you generate index and leave this, but if you have MUCH MORE changes on exp than ORDER by exp you need then think about don't indexing this column because it was in overall longer. Or use other solution like SELECT ordered by exp caching?

Propably caching result of SELECT is the best solution for this problem, but do this select without ADDING and DROPING INDEX because this don't have any sense.

Answer 3

Artefacto is right, and worse, altering a table is a blocking procedure, meaning that all your selects and updates are going to pile up while that's going on. If you have any sort of load on your database, this will destroy it. Either use the index or don't, but don't build and destroy it.

Answer 4

SELECT * FROM players ORDER BY `exp` DESC

This requires reading the whole table and sorting it.

mysql_query("ALTER TABLE `players` ADD INDEX ( `exp` )");
$query = mysql_query("SELECT * FROM players ORDER BY `exp` DESC");
mysql_query("ALTER TABLE `players` DROP INDEX `exp`");

This requires reading the whole table, sorting it, writing the results of the sort to the disk, reading them back from the disk, then removing them: all that having the table locked.

The first option is much faster and better for concurrency.

It will be yet faster if you create a permanent index.

10k records is far too few to worry about DML performance.