problem description

1.mysql in collection query is too inefficient and takes too long.

the environmental background of the problems and what methods you have tried

user_id and physicion are the user fields associated with this table. Because the superior is required to see the subordinate, (99,166,96) this collection is the subordinate user Id collection.
in query collection is too inefficient and does not know the solution. The collection is looked up from another database, so the query cannot be associated.

related codes

/ / Please paste the code text below (do not replace the code with pictures)
SELECT

a.id,
a.case_id AS caseId,
a.create_time AS createTime,
a.case_state AS caseState,
a.ealigner_treatment_plan AS ealignerTreatmentPlan,
a.patient_name AS patientName,
a.remarks,
a.is_valid AS isValid,
b.fileUrl 

FROM

cm_caseinfo AS a
LEFT JOIN cm_filesinfo AS b ON b.caseInfo_id = a.case_id 
AND b.fileTypeKey_id = 9 
AND b.`status` = 1 

WHERE

( a.user_id OR a.physicion IN (99, 166, 96) ) 
AND a.is_valid = 1 

ORDER BY

a.create_time DESC 
LIMIT 10

CREATE TABLE cm_caseinfo (
id varchar (50) NOT NULL COMMENT "key Id",
address varchar (255) DEFAULT NULL COMMENT" address,
age int (11) DEFAULT NULL COMMENT "age (background calculation)",
birthday varchar (255) DEFAULT NULL COMMENT "birthday required",
case_id varchar (255) NOT NULL COMMENT "case number (time + serial number)" 1 normal, 2 delete",
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT "creation time",
ealigner_treatment_plan int (11) DEFAULT"0" COMMENT "treatment plan (1, lingual side, 2 transparent, 3 lip side)",
gender int (11) DEFAULT NULL COMMENT "gender (0, male, 1, Female)",
patient_name varchar (255) DEFAULT NULL COMMENT "patient name",
patient_name_pingyin varchar (255) DEFAULT NULL COMMENT "pinyin",
phone varchar (255) DEFAULT NULL COMMENT "patient phone (compatible previous database)",
physicion int (10) DEFAULT NULL COMMENT "attending physician ID",
profession varchar),
treament_date
update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "update time",
user_id int (11) DEFAULT NULL COMMENT "operator Id",
remarks varchar (255th) DEFAULT NULL COMMENT" remarks,
operator_ip varchar) (255) DEFAULT NULL COMMENT "operator IP",
operator_id varchar (255) DEFAULT NULL COMMENT" operator name",
is_valid tinyint (11) tinyint "1mm, < DEFAULT >
PRIMARY KEY ( id ) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;