Level 102
Use explain to see what index is used. If no indexes exist, you should really make some :)
Mar 2, 2022
10
Level 1
Query Optimization
I run this query it't take 7-8 second there are more than 1 million data in cars and cars stocks
SELECT cars.id, cars.name, car_stocks.created_at FROM carsLEFT JOIN car_stocks ON car_stocks.car_id = cars.id and date(car_stocks.created_at) = '2022-01-18'
Please suggest if you have a more optimized/less time consuming query
Level 1
I have added index but still takes time
Level 102
@VinayPrajapati Use explain to check that your index is used
Level 1
@Sinnbeck
Please check
actual scenario on my project
Main Table
Child Table
Result with secound
Extra detail
Level 104
@VinayPrajapati did you try my suggestion to avoid using the DATE function?
Level 1
Ya but still takes time
Level 104
@VinayPrajapati any improvement at all?
Level 1
Not still takes time
Level 73
@VinayPrajapati Have you created an index on the created_at column in car_stocks?
You can read up on indexes here https://tray2.se/posts/database-design-part-2
1 like
Level 104
Using a function here is non-ideal:
date(car_stocks.created_at)
Consider:
SELECT cars.id, cars.name, car_stocks.created_at
FROM cars
LEFT JOIN car_stocks
ON car_stocks.car_id = cars.id
AND car_stocks.created_at BETWEEN '2022-01-18' AND '2022-01-19';
Please or to participate in this conversation.