mysql query with select - where - in - join

Question

Which is the better way of retrieving multiple rows?

select * 
  from order_details 
 where order_id in (1,2,3, ... .... )

...or:

     select *
      from order_details 
INNER JOIN orders on orders.id = order_details.order_id 
INNER JOIN customers on customers.id = orders.customer_id
     where customers.id = 3

Answer 1

I would recommend trying them both and doing a benchmark. Since it can depend on indexing, column size and row count.

Shouldn't be hard, since for example phpMyAdmin shows execution time for each query it does.

Answer 2

The first version selects from one table, so it has less work to do.

However, if the second query produces the correct answer, then you have to do the work with the other two tables to establish the list of order_id values to generate the first statement, in which case the aggregate workload is greater and you would be better off using the second statement overall.

In general, let the SQL optimizer optimize; it is better at optimizing than you are, and it works best when given the whole problem to solve rather than pieces of the problem.

(The second option will usually out-perform the total workload for the first, if only because the DBMS doesn't have to send the intermediate results back to the client and parse and optimize two separate statements.)

Also, the second statement has a vastly bigger row size for the result set (as the '*' covers the columns from 3 tables instead of 1). This really isn't a fair comparison of two equivalent SQL statements.