Should I add and delete index in one page?

Let's say I have players table. It consists with 3 rows(it has much more, but let's suppose it has only 3). member_id, name, exp. I use member_id row in every page so that's why I added index only to member_id. But I want to make a top players' list in one page wi开发者_如何转开发th the highest exp. So I do something like that:

$query = mysql_query("SELECT * FROM players ORDER BY `exp` DESC");

If I have 10k players, I can't run query like this without adding index to exp. So my question is, should I do like this:

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`");

Or there is something else better I can do? Because adding and removing indexes is quite expensive. But probably I could do cache every 10 minutes for example.

---

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.

---

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.

---

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.

---

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.