La query mi sembra giusta, a parte il "count(distinct id)" che non ha molto senso, puoi sostituirlo con count(*) ma non so se avrai dei miglioramenti.
Da quello che dici mi sorge il sospetto che tu non abbia un indice su X...