seon99il

Database

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

Comments