SQL Select query is taking so long time on new Server 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.
DISTINCT is not a function.  It applies to the whole row.  And it is probably unnecessary.  It takes an extra pass over the data to perform the DISTINCT functionality.
LIMIT 200000 seems quite unrealistic.  What is the goal?
The query, without a WHERE clause, will scan the entire tbcc_l_lists table.  That takes time if the table is big.
COUNT(*) is the usual pattern.  COUNT(x) is often misused; this means "count the row if x is NOT NULL.  Or did you really want that test?
Please provide EXPLAIN SELECT so we can see what parts are being inefficient.
That may lead to requesting SHOW CREATE TABLE for each table.
You say the configuration is the same -- are you referring to both the memory size and my.cnf?