Order of columns in a primary key, performance

Question

I have a small question for performance reasons.

I'm working with symfony and doctrine. I always used annotations in my entities and decided recently to switch to yml files.

So I exported externally all my entities and generated the yml files.

I compared the yml files with the database. There was a diff file generated which drops the primary key on certain tables and then adds them, simply in a different order. These primary keys have multiple columns.

It seems that this happens only when one of the columns is a foreign key.

The question is whether I can execute the change to my database and switch the order of the key columns, or whether it will affect my performance?

Answer 1

Primary keys in MySQL are implemented with unique indexes. Indeed, that's true for most, if not all, SQL dbms nowadays.

The order of columns in an index is significant. Changing the order can certainly change performance.

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

There might be a good reason for changing the order. See Using Foreign Key Constraints.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint.

If your programs are putting the foreign key columns first in the new primary key, this might be the problem they're trying to solve. They're trying to avoid creating both an index on the primary key columns and an additional index on the foreign key columns alone.

That doesn't mean it won't hurt performance of particular queries, though.

There are at least two ways to test this. First, you can bring up a new database, connect your application to it, and run it. Does it seem fast enough?

Second, you can bring up a new database, and run some or all of your queries manually, using EXPLAIN.