SELECT * FROM (
SELECT a.appointment_made_date_time as timestamp, a.appoimentid as deleteid, tx.treatment_name, tc.treatment_duration, a.clientname,a.clientmail,a.clientphone, a.date, a.time, w.workername, 'create' AS event, (SELECT COUNT(*) FROM appointments WHERE clientmail = a.clientmail OR clientphone = a.clientphone OR clientip = a.clientip OR identifier_id = a.identifier_id) as amount,(SELECT COUNT(*) FROM blacklist_request WHERE clientmail = a.clientmail OR clientphone = a.clientphone OR ip_address = a.clientip OR identifier_id = a.identifier_id) as blacklisted FROM appointments AS a LEFT JOIN treatments_connection AS tc ON a.treatment_connection_id = tc.connection_id LEFT JOIN barberworkeraccounts AS w ON tc.worker_id = w.workerid LEFT JOIN treatments AS tx ON tc.treatment_id = tx.treatment_id WHERE a.barberid = 58 AND RIGHT(a.clientip,2) != '.x'
UNION
SELECT a.appointment_made_datetime as timestamp, a.deleteid as deleteid, tx.treatment_name,tc.treatment_duration, a.clientname,a.clientmail,a.clientphone, a.appdate as date, a.apptime as time, w.workername, 'create' AS event, 'null' AS amount, 'null' AS blacklisted FROM deleted AS a LEFT JOIN treatments_connection AS tc ON a.treatment_connection_id = tc.connection_id LEFT JOIN barberworkeraccounts AS w ON tc.worker_id = w.workerid LEFT JOIN treatments AS tx ON tc.treatment_id = tx.treatment_id WHERE a.barberid = 58 AND RIGHT(a.appointment_made_ip,2) != '.x'
UNION
SELECT a.deleted_datetime as timestamp, a.deleteid as deleteid, tx.treatment_name, tc.treatment_duration, a.clientname,a.clientmail,a.clientphone, a.appdate as date, a.apptime as time, w.workername, 'delete' AS event, 'null' AS amount, 'null' AS blacklisted FROM deleted AS a LEFT JOIN treatments_connection AS tc ON a.treatment_connection_id = tc.connection_id LEFT JOIN barberworkeraccounts AS w ON tc.worker_id = w.workerid LEFT JOIN treatments AS tx ON tc.treatment_id = tx.treatment_id WHERE a.barberid = 58 AND RIGHT(a.appointment_deleted_ip,2) != '.x'
) entries
GROUP BY deleteid, event
ORDER BY timestamp DESC
LIMIT 90
AS i am trying to run this query the subquery is too slow. sometimes it takes 30 seconds or more.
Here is the subquery which causes the issue:
(SELECT COUNT(*) FROM appointments WHERE clientmail = a.clientmail OR clientphone = a.clientphone OR clientip = a.clientip OR identifier_id = a.identifier_id) as amount
In appointments table there are more than 40K rows. and another tables have near about 300 to 400 rows.
Is there any replacement for not using subquery?
