MySQL query performance implementing ORDER BY on large dataset and multiple conditions
I'm wondering if anyone has experience with I've been working on this all day and I'm currently working with a performance scenario with a MySQL query that includes an `ORDER BY` clause on a large dataset..... I have a table named `orders` with around 2 million records that contains columns `order_id`, `customer_id`, `order_date`, and `total_amount`. I need to fetch the latest 100 orders for a specific customer and sort them based on `total_amount` in descending order. Here's the query I've been using: ```sql SELECT * FROM orders WHERE customer_id = 12345 ORDER BY total_amount DESC, order_date DESC LIMIT 100; ``` The scenario arises when I run this query; it takes an unusually long time (over 15 seconds) to execute. I've tried adding an index on `customer_id`, but it hasn't improved performance significantly. The output I expect is the latest 100 orders for customer 12345, sorted first by the total amount, then by order date in case of ties. Additionally, I sometimes encounter a timeout behavior when running this query through my application, which is built with Laravel 8.0 and uses Eloquent ORM. I thought about optimizing this further by creating a composite index on `(customer_id, total_amount, order_date)`, but I'm unsure if that would solve the performance scenario. Is there a better approach to optimize this query? Also, are there any common practices I might be missing that could help improve the execution time? My development environment is Windows 11. Is there a simpler solution I'm overlooking? I've been using Sql for about a year now.