Mysql – SQL Select query is taking so long time on new Server

MySQLmysqldumpqueryquery-performanceselect

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.

Best Answer

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?

Related Solutions

Sql-server – SQL Select taking too much time to execute

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN. As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying: JoinedTable.WhereColumn IN (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows? just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.

Mysql – SELECT with large WHERE IN clause taking long time

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN:

SELECT x.* 
FROM xrays AS x
  JOIN appointments AS a
    ON a.patID = x.patID 
WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn') 
ORDER BY x.patID                   --- optional so your application gets
                                   --- the data of patients, ordered.

Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your docID, patID, should have an index.

Best Answer

Related Solutions

Sql-server – SQL Select taking too much time to execute

Mysql – SELECT with large WHERE IN clause taking long time

Related Question