我有一个复杂的sql查询可以工作，但是它的性能不太理想（执行几乎需要3秒钟）。我想我已经把我能想到的都优化了，但也许还有更多的东西我仍然抓不到。
在这里：

SELECT DISTINCT doctors.doc_id, doctors.doc_title, doctors.doc_last, 
    doctors.doc_first, doctors.doc_email, doctors.doc_birthday,
    doctors.doc_mobile, doctors.doc_occasional, doctors.doc_fiscal_code, 
    doctors.doc_register_number, doctors.doc_register_province, 
    doctors.doc_agreement, doctors.doc_notes, doctors.doc_timestamp,
    doctors.doc_deleted, specializations.spe_id, specializations.spe_name,
    activities.act_id, activities.act_name,
    users.use_id, users.use_last, users.use_first, users.use_active,
    (SELECT COUNT(*)
    FROM congress
    INNER JOIN participants ON participants.par_congress = congress.cng_id
    WHERE par_doctor = doc_id
    AND congress.cng_from >= '2018-01-01'
    AND congress.cng_from <= '2018-07-02')
    AS cng_count,
    (SELECT COUNT(*)
    FROM visits
    INNER JOIN reports ON reports.rep_id = visits.vis_report
    INNER JOIN locations ON locations.loc_id = visits.vis_location
    WHERE visits.vis_doctor = doctors.doc_id 
    AND locations.loc_structure LIKE '%' 
    AND reports.rep_dated >= '2018-01-01' 
    AND reports.rep_dated <= '2018-07-02')
    AS vis_count_all,
    (SELECT COUNT(*)
    FROM visits
    INNER JOIN reports ON reports.rep_id = visits.vis_report
    INNER JOIN locations ON locations.loc_id = visits.vis_location 
    WHERE visits.vis_doctor = doctors.doc_id 
    AND reports.rep_user = users.use_id 
    AND locations.loc_structure LIKE '%' 
    AND reports.rep_dated >= '2018-01-01' 
    AND reports.rep_dated <= '2018-07-02')
    AS vis_count_user,
    (SELECT COUNT(*)
    FROM locations
    WHERE locations.loc_doctor = doctors.doc_id )
    AS loc_count
    FROM doctors
    LEFT JOIN locations ON locations.loc_doctor = doctors.doc_id
    INNER JOIN specializations ON specializations.spe_id = doctors.doc_specialization
    INNER JOIN activities ON activities.act_id = doctors.doc_activity
    INNER JOIN users ON users.use_id = doctors.doc_user 
    WHERE doctors.doc_last IS NOT NULL 
    AND doctors.doc_id LIKE '%' 
    AND (locations.loc_province IS NULL OR locations.loc_province LIKE '%') 
    AND (locations.loc_structure IS NULL OR locations.loc_structure LIKE '%') 
    AND DATE(doctors.doc_timestamp) <= '2018-07-02' 
    AND doctors.doc_occasional LIKE '%' 
    AND doctors.doc_deleted LIKE '0' 
    AND doctors.doc_agreement LIKE '%' 
    AND doctors.doc_active 
    AND users.use_id LIKE '%' 
    GROUP BY doctors.doc_id 
    HAVING vis_count_user <> - 1 
    ORDER BY doctors.doc_last, doctors.doc_first, doctors.doc_id

真正的瓶颈在于 vis_count_all 以及 vis_count_user 子选择（它们仅在附加的 AND reports.rep_user = users.use_id 语句）：删除它们可以加快查询速度
因为它们只在一个语句中不同，所以我不知道是否可以重用其中一个，以更简单的方式获得另一个值
不管怎样，我创造了所有我能想到的钥匙，这是解释结果

请问，有什么改进意见吗？谢谢您