I've been inspecting the query. It retrieves more fields from the database than is displayed in the list view. At least one join could be skipped, since there is no modified-by information. To speed things up and reduce the execution time by 50% remove all the unnecessary fields and the the query could be:
Code:
SELECT a.id, a.venue, a.alias, a.url, a.street, a.city, a.state, a.country, a.created_by, a.published, a.checked_out, a.checked_out_time, a.ordering, uc.name AS editor, u.email, u.name AS author,COUNT(e.locid) AS assignedevents
FROM `#__jem_venues` AS a
LEFT JOIN #__users AS uc ON uc.id = a.checked_out
LEFT JOIN #__users AS u ON u.id = a.created_by
LEFT OUTER JOIN #__jem_events AS e ON e.locid = a.id
WHERE a.published IN (0, 1)
GROUP BY a.id
ORDER BY a.venue asc
LIMIT 25
It can be even more efficient if the list view had even less columns. Do we need the state or the creator email in the list view?
But it's mainly the count of the events that is the problem here.
