How To determine the order of indexes when using Composite Key
1 min read
Sometimes, PK columns can be created without considering the order when creating a project Because use data modeling tools to model and create DDL
in this case, the following problems may be accompanied
- When creating index, PK can be generated in an unintended sequence
- performance degradation occurs when select data
- Many indexes are generated, Increase unnecessary internal work when entering / modifying / deleting
How to solve it?
must analyze SQL “where” clause, And adjust PK column order
I’ll explain it with a simple example

TABLE : ORDER_INFO

Query we will check
First, apply the index in order of ORDER_DATE and SELLER_ID

ADD DATE_ID_INDEX

DATE_ID_INDEX

TABLE
As can see, it’s arranged in order of ORDER_DATE and SELLER_ID Sort by ORDER_DATE first, sort by SELLER_ID
Now run the query query 1 is executed first, and query 2 is executed next. like this

query 1

Results of query 1
A full scan occurs when looking for “10368” This is not good, cause performance degradation

query 2
Then, I’ll write “SELLER_ID” on the front Delete a previously created index

DROP DATE_ID_INDEX

ADD ID_DATE_INDEX

ID_DATE_INDEX

TABLE
I can see that it’s applied well Now let’s run the query again

query 1

Results of query 1
As shown, “10368” can be found in a narrower range

query 2
When setting up Composite Key, you must sort the indexes according to the query