Within search-box when I searched for "deactivate" term than it take approx 30 sec to load result
After going through slow query logs I found when searching for "deactivate" term than it execute two queries were both query take approx 12-13 sec to complete execution total 26 sec both query take to process
select count(*) as aggregate from conversations inner join threads on conversations.id = threads.conversation_id left join customers on conversations.customer_id = customers.id where conversations.mailbox_id in (6, 1, 2, 5, 3, 4) and (conversations.subject like '%deactivate%' or conversations.customer_email like '%deactivate%' or conversations.id = 0 or conversations.id = 0 or customers.first_name like '%deactivate%' or customers.last_name like '%deactivate%' or threads.body like '%deactivate%' or threads.from like '%deactivate%' or threads.to like '%deactivate%' or threads.cc like '%deactivate%' or threads.bcc like '%deactivate%') group by conversations.id;
select conversations.* from conversations inner join threads on conversations.id = threads.conversation_id left join customers on conversations.customer_id = customers.id where conversations.mailbox_id in (6, 1, 2, 5, 3, 4) and (conversations.subject like '%deactivate%' or conversations.customer_email like '%deactivate%' or conversations.id = 0 or conversations.id = 0 or customers.first_name like '%deactivate%' or customers.last_name like '%deactivate%' or threads.body like '%deactivate%' or threads.from like '%deactivate%' or threads.to like '%deactivate%' or threads.cc like '%deactivate%' or threads.bcc like '%deactivate%') group by conversations.id order by last_reply_at desc limit 50 offset 0;
There is also some duplication or repetition. I don't know how this both query are related to each-other because result on first query is not being used in second.
I hope so their is place of optimization which can gradually reduce the search time.
Within search-box when I searched for "deactivate" term than it take approx 30 sec to load result
After going through slow query logs I found when searching for "deactivate" term than it execute two queries were both query take approx 12-13 sec to complete execution total 26 sec both query take to process
select count(*) as aggregate from
conversations
inner jointhreads
onconversations
.id
=threads
.conversation_id
left joincustomers
onconversations
.customer_id
=customers
.id
whereconversations
.mailbox_id
in (6, 1, 2, 5, 3, 4) and (conversations
.subject
like '%deactivate%' orconversations
.customer_email
like '%deactivate%' orconversations
.id
= 0 orconversations
.id
= 0 orcustomers
.first_name
like '%deactivate%' orcustomers
.last_name
like '%deactivate%' orthreads
.body
like '%deactivate%' orthreads
.from
like '%deactivate%' orthreads
.to
like '%deactivate%' orthreads
.cc
like '%deactivate%' orthreads
.bcc
like '%deactivate%') group byconversations
.id
;select
conversations
.* fromconversations
inner jointhreads
onconversations
.id
=threads
.conversation_id
left joincustomers
onconversations
.customer_id
=customers
.id
whereconversations
.mailbox_id
in (6, 1, 2, 5, 3, 4) and (conversations
.subject
like '%deactivate%' orconversations
.customer_email
like '%deactivate%' orconversations
.id
= 0 orconversations
.id
= 0 orcustomers
.first_name
like '%deactivate%' orcustomers
.last_name
like '%deactivate%' orthreads
.body
like '%deactivate%' orthreads
.from
like '%deactivate%' orthreads
.to
like '%deactivate%' orthreads
.cc
like '%deactivate%' orthreads
.bcc
like '%deactivate%') group byconversations
.id
order bylast_reply_at
desc limit 50 offset 0;There is also some duplication or repetition. I don't know how this both query are related to each-other because result on first query is not being used in second.
I hope so their is place of optimization which can gradually reduce the search time.