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

Question

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!

Answer 1

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()