We have two tables (Customer and Order) table. Is there any performance difference between these two queries given below.

1. Customer table has customer details.(customerId, customerdetails)
2. Order table has Order details of the customer(orderId, customerId, orderdetails) i.e customer id will be duplicated here and not nullable with ON_DELETE_CASCADE foreign key.

eg query:

select * from Order where customerId in (1,2,3,4,....) 

or

 select Order.* from Order inner join customer on 
 Order.customerId = customer.customerId where customer.customerId 
 in (1,2,3,4,....)

CodePudding user response：

The first one does less work: it only references one table. Therefore it's faster.

The second one has an INNER JOIN. That means it must check each Order row to ensure it has a matching Customer row. (If a Customer is DELETEd, her orders won't appear in the result set.) Rows that don't match must not appear in the result set. Doing that check takes at least some work. But if your tables are small you'll probably be unable to measure any significant difference.

You can investigate this yourself by prefixing EXPLAIN to each query. It tells you how the query planner module will satisfy the query. Your second query will have two EXPLAIN rows.

Indexes will help. Your first query will benefit if you create this one:

ALTER TABLE Order CREATE INDEX customerId (customerId);

Welcome to Stack Overflow. When you have other query-optimization questions, you probably should read this.

CodePudding user response：

A General Rule: One query involving two tables and a JOIN will be faster than two queries where you are handing ids from the first to the second. And it is less code (once you are familiar with Joins).

Why? A roundtrip from the client to the server takes some effort. Two roundtrips is slower than one.

Does it matter? Not a lot. Usually, the difference is millisecond(s), maybe less.

Always? No. That is why I said "General Rule".