Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes?
Please use these table/column names in your answer:
- customer:
id,name - purchase:
id,customer_id,item_id,date
And in more complicated situations, would it be (performance-wise) beneficial to denormalize the database by putting the last purchase into the customer table?
If the (purchase) id is guaranteed to be sorted by date, can the statements be simplified by using something like LIMIT 1?
