Join sql performance consideration-CodePudding

So i have three tables with column:

merchant:
-id
-name

store:
-id
-merchantId
-name

transaction
-id
-merchantId
-storeId

i want to ask, which is better performance to get merchant detail, first we remove merchantId on transaction table and perform join query with storeId then join merchantId or we don't remove merchantId and perform join query to each table based on storeId and merchantId on transaction table?

we assume store and transaction have a lot of data

CodePudding user response：

In here you used normalization for the data and it's fine that you have three tables but going for more normalization means it will take a performance hit when receiving data. So what you can do here is use pagination. To use pagination in MySQL you can use LIMIT and OFFSETkeywords. You can use below ways,

SELECT
 merchant.name,
 store.name,
 transaction.storeID
FROM merchant
JOIN store
ON merchant.id = store.merchantId
JOIN transaction
ON transaction.storeId= store.id ORDER by merchant.id asc LIMIT 100;

Here you will get 1-100 values.

SELECT
 merchant.name,
 store.name,
 transaction.storeID
FROM merchant
JOIN store
ON merchant.id = store.merchantId
JOIN transaction
ON transaction.storeId= store.id ORDER by merchant.id asc LIMIT 100 OFFSET 20;

Here you will get 21-120 values

CodePudding user response：

The Transaction table should not include the merchantId column. A data model should not permit multiple paths between two objects