CODEWITHSUNDEEP
mysqlindexing
Dylan
Dylan

Reputation: 9383

MySQL - rearrange the order of the indexes in a table?

Is it possible to rearrange the indexes in a MySQL table?

I have a big table with about 20 indexes on it, but as I created them in the course of a few years, they're not logically ordered anymore. I would like them to have the same order as the columns in the table. I use Navicat, but I don't see an option to move an index up or down in the list. Is it even possible?

UPDATE: I just found out that, in PostgreSQL, I can't even change the order of columns, let alone indexes! And to all those people who say I have to use views, and that the column order is not important: of course it is! Columns and indexes should be logically ordered in the design interface. Most people use GUIs nowadays to edit their tables!

I just can't believe something basic like this is not implemented. It's 2011, guys!

Upvotes: 5

Views: 4431

Answers (1)

Alister Bulman
Alister Bulman

Reputation: 35169

It's not possible to change the order of indexes. It may not matter internally within MySQL, but if you did want to change the order, the only way is to drop and re-create them as required.

Having so many indexes, as @btilly says, might not be the best idea. I don't know what data you have, or the data access patterns that would require indexed access, but it does sound a lot. OTOH, it might just be exactly right. Only someone with access to the DB and has profiled it would know.

Upvotes: 2

Related Questions