ORDERBY减慢mysql查询速度

我们有一个问题会减慢整个过程。

此查询需要大约 10 seconds

SELECT tbl_organisation.*,  
(
    SELECT count(*)
    FROM tbl_user_organisation 
    WHERE tbl_user_organisation.organisation_id = tbl_organisation.id
) as members 
FROM tbl_organisation
WHERE tbl_organisation.archived = 0 
AND tbl_organisation.name LIKE "%name%"  
ORDER BY members
LIMIT 10 OFFSET 0;

ORDER BY 76ms

SELECT tbl_organisation.*,  
(
    SELECT count(*)
    FROM tbl_user_organisation 
    WHERE tbl_user_organisation.organisation_id = tbl_organisation.id
) as members 
FROM tbl_organisation
WHERE tbl_organisation.archived = 0 
AND tbl_organisation.name LIKE "%name%"  
LIMIT 10 OFFSET 0;

现在的问题是:有没有可能让这个查询更快?如果是,怎么做?

自从 members 是动态计数的,我们无法添加 INDEX 如其他解决方案中所建议的那样加快查询速度。

EXPLAIN