How else to optimize a query with a subselect?

Question

PHP

Good day to all.
There is a task: We have a lot of cities and announcements of events in them. You need to display a list of all cities that have ever had announcements and next to it, the number of upcoming (greater than or equal to the current date) announcements in a particular city.
There is a request like this:

select c.id, c.name, count(n.id) from main_city c left join main_news n on (c.id=n.city and n.moder=1 and n.date>=DATE_FORMAT($now_city_date,'%Y%m%d') )
group by c.id order by c.sort,c.name

I optimized it as best I could, but it still takes 3-5 seconds, initially it generally reached 10.
Can I speed it up somehow?

Answer 1

First of all - are the keys okay?

Answer 2

Sphinx will be 1000 times faster.. Of course, I understand that this is shooting at sparrows from a cannon.. but still..
Analyze your request, it takes a long time... maybe the file system is loaded or something else.. look as they say.