Very slow query, any other ways to format this with better performace?

mysql, sql

Solution

Appart from the obvious indexes needed, I don't really get why you are joining your table with itself for choosing the `IN` condition. The `ORDER BY` is also not needed. Are you sure that your query can't be written just like this?:

SELECT COUNT(*) AS num
FROM `fl_events` AS e1
WHERE e1.startdate >= now()

Problem

I have this query (I didn't write) that was working fine for a client until the table got more then a few thousand rows in it, now it's taking 40 seconds+ on only 4200 rows. Any suggetions on how to optimize and get the same result? I've tried a few other methods but didn't get the correct result that this slower query returned... ``` SELECT COUNT(*) AS num FROM `fl_events` WHERE id IN( SELECT DISTINCT (e2.id) FROM `fl_events` AS e1, fl_events AS e2 WHERE e1.startdate >= now() AND e1.startdate = e2.startdate ) ORDER BY `startdate` ``` Any help would be greatly appriciated!

Original source