Query takes so long in MySQL

Question

I have 3 models called stores, customers, subscriptions.

subscription has two foreign keys from store and customer models and also has start_date and end_date.

The tables are pretty simple. store only has id and name same as customers.

I'm running this query.

SELECT subscription_subscription.store_id, COUNT(*) AS sub_store
FROM subscription_subscription 
WHERE CURRENT_DATE() <= subscription_subscription.end_date
GROUP BY subscription_subscription.store_id 
ORDER BY sub_store DESC

And here it is: 621760 total, Query took 9.6737 seconds.

All of tables have 1 million rows.

But when I remove the WHERE CURRENT_DATE() <= subscription_subscription.end_date query takes 0.3177 seconds.

How can I optimize date comparison?

Answer 1

You can try these two things:

1. use a variable to store CURRENT_DATE() and use this variable in query instead of function
2. Create an index on end_date which includes store_id