Order of indexes question

I’m working on a script to convert a latin1 database to utf8 (don’t try this at home -- it will drive you nuts). I’m trying to make it work on any DB, not just the MODx one.
There are a number of scripts available to do this already, but in testing them, I found that compound (composite) keys, comments in the DB, UNIQUE and NOT NULL settings were all lost. Apparently none of the authors thought to do a diff on the before and after SQL dumps.

My script converts all the text columns to blobs, alters the character sets, and converts them back to what they were. Then it restores all the lost info.

It’s almost finished and it duplicates the database structure perfectly, with one exception.

On a couple of tables, the order of the indexes is changed:


is converted to:



I think it has to do with restoring the NOT NULL and UNIQUE properties of the fields and would I think it would be a bear to fix in a generic way.

In MODx, it only affects the user_settings and web_user_settings tables and it can’t happen with compound indexes, which will always be last.

My question is: does the order of the indexes matter at all?

The order should not matter, other than within a single index definition, i.e. the order of columns used within a compound index does matter.

Quote from: OpenGeek at Apr 12, 2010, 01:43 PM

The order should not matter, other than within a single index definition, i.e. the order of columns used within a compound index does matter.

Thanks. That made me realize that the site_content compound index is probably correct by chance because the fields are in the same order as they are in the index. I’ll have to figure out some way to order them based on the Seq_in_index value (ugh) if I want a generic solution.