Column order in primary key, performance
I have a small question for performance reasons.
I am working with symfony and doctrine. I have always used annotations in my entities and recently decided to switch to yml files.
So, I exported all my entities from the outside and generated yml files.
I have been comparing the yml files to the database. A diff file was created that transfers the primary key to specific tables and then adds them just in a different order. These primary keys have multiple columns.
This only seems to happen when one of the columns is a foreign key.
The question is, can I make a change in my database and switch the order of the key columns, or will this affect my performance?
source to share
Primary keys in MySQL are implemented with unique indexes. Indeed, this is true for most, if not all, SQL dbms today.
The order of the columns in the index is significant. Changing the order can certainly change performance.
MySQL can use multi-column indexes for queries that check all columns in the index, or queries that check only the first column, the first two columns, the first three columns, etc. If you list the columns in the correct order in the index definition, a single composite index can speed up multiple kinds of queries on the same table.
There may be a good reason for changing the order. See Using Foreign Key Constraints .
MySQL requires indexes on foreign keys and reference keys so foreign key checks can be fast and do not require table scans. in the referenced tables, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is automatically created in the reference table if it does not exist. This index could be disabled later if you create another index that can be used to enforce a foreign key constraint.
If your programs put foreign key columns into a new primary key first, this may be the problem they are trying to solve. They try to avoid creating an index on the primary key columns and an additional index on only foreign key columns.
This does not mean that it will not hurt the performance of certain queries.
There are two ways to check this. First, you can create a new database, connect your application to it, and run it. Does this seem fast enough?
Second, you can open a new database and run some or all of your queries manually using EXPLAIN .
source to share