SQL Select query is taking so long time on new Server

by Akash Sharma   Last Updated June 01, 2020 11:06 AM

I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point:

One of query which is running within a second on live server, it's taking around 5 mins to execute in aws server.

I have already checked about configuration of mysql and its same. We keep same configuration as live for mysql, Tried to import database thrice but same problem persist:

Here is mysql Versions of both server: Rackspace - 5.5.61 AWS - 5.7.29

Here is that query:

"SELECT DISTINCT(listname), tbcc_l_lists.id, tbcc_l_lists.reportcode, tbcc_l_lists.list_type, tbcc_l_lists.list_url, tbcc_l_lists.researchby, tbcc_l_lists.linkassigndate, tbcc_l_lists.startdate, tbcc_l_lists.completeddate, tbcc_l_lists.team, tbcc_l_lists.status, tbcc_l_lists.priority, tbcc_l_lists.list_url_path, tbcc_l_lists.comments, tbcc_l_lists.added_by, tbcc_l_categorygroup.name, tbcc_l_listtype.listtype_name, (SELECT COUNT(tbcc_l_contacts.primary_list) FROM tbcc_l_contacts WHERE tbcc_l_lists.id=tbcc_l_contacts.primary_list ) AS contactcount, (SELECT COUNT(tbcc_l_contacts_rejects.primary_list) FROM tbcc_l_contacts_rejects WHERE tbcc_l_lists.id=tbcc_l_contacts_rejects.primary_list) AS rejectcount, tbcc_l_admins.username, tbcc_l_admins.fname, tbcc_l_admins.lname, admins.username AS team_username, admins.fname AS team_fname, admins.lname AS team_lname, admins1.username AS addedby_username, admins1.fname AS addedby_fname, admins1.lname AS addedby_lname FROM tbcc_l_lists LEFT JOIN tbcc_l_categorygroup ON tbcc_l_lists.categorygroup = tbcc_l_categorygroup.id LEFT JOIN tbcc_l_listtype ON tbcc_l_lists.list_type = tbcc_l_listtype.listtype_id LEFT JOIN tbcc_l_contacts ON tbcc_l_lists.id = tbcc_l_contacts.primary_list LEFT JOIN tbcc_l_contacts_rejects ON tbcc_l_lists.id = tbcc_l_contacts_rejects.primary_list LEFT JOIN tbcc_l_admins ON tbcc_l_admins.id = tbcc_l_lists.researchby LEFT JOIN tbcc_l_admins AS admins ON admins.id = tbcc_l_lists.team LEFT JOIN tbcc_l_admins AS admins1 ON admins1.id = tbcc_l_lists.added_by WHERE (1=1) LIMIT 200000;"

I am working on this issue since last couple of days, Any help will be highly appreciable.

Related Questions

Select query taking too long MYSQL 5.6.17

Updated December 19, 2017 09:06 AM