I’m trying to figure out why the query is taking too long to execute on the server when compared to my laptop. Both the server and laptop are running MySQL 5.1.36 on WAMP 2.0 and have the same database schema, as I have exported it from the server database and imported it into the laptop’s database. It takes 0.031 seconds to execute on the laptop while 82.447 seconds to execute on the server!
Laptop Specifications
Operating System: Microsoft Windows 7 Professional, Processor: Intel® Core™ i7-4600M Processor (4M Cache, up to 3.60 GHz), Memory: 8GB
Server Specifications
Operating System: Microsoft Windows 2008 Standard SP2, Processor: Intel® Xeon® Processor X5570 (8M Cache, 2.93 GHz, 6.40 GT/s Intel® QPI), Memory: 4GB
Here are screenshots of the CPU utilization on the server of when the query is being executed and when not. Bear in mind, that the server is a VM running in a server farm.
Below is the query and the each table has no more than 1000 rows.
SELECT form.id AS `Reference No.`,
DATE_FORMAT(form.sbmt, '%d %b %Y') AS `Submission Date`,
DATE_FORMAT(form.sbmt, '%h:%i %p') AS `Submission Time`,
department.name AS `Department`,
section.name AS `Section`,
reporting_to_1.id AS `Reporting To 1 - System ID`,
reporting_to_1.name_ldap AS `Reporting To 1 - Name`,
reporting_to_1_department.name AS `Reporting To 1 - Department`,
CONVERT(IFNULL(reporting_to_2.id, '') USING utf8) AS `Reporting To 2 - System ID`,
IFNULL(reporting_to_2.name_ldap, '') AS `Reporting To 2 - Name`,
IFNULL(reporting_to_2_department.name, '') AS `Reporting To 2 - Department`,
form_type.des AS `Form Type`,
CONVERT(IF(form.subno = 0, '-', form.subno) USING utf8) AS `Subscriber No.`,
form.subname AS `Subscriber Name`,
form.incidentdate AS `Incident Date`,
form.rsndes AS `Reason`,
form.amount AS `Amount`,
form.des AS `Description`,
CONCAT('http://cns/attach/', attachment_1.path, '/', attachment_1.filename_generated) AS `Attachment - 1`,
CONCAT('http://cns/attach/', attachment_2.path, '/', attachment_2.filename_generated) AS `Attachment - 2`,
IFNULL(info.name, '') AS `Agent Info - Name`,
CONVERT(IFNULL(info.emp_id, '') USING utf8) AS `Agent Info - ID`,
IFNULL(info.tabs, '') AS `Agent Info - TABS`,
IFNULL(info.type, '') AS `Agent Info - Type`,
CONVERT(IFNULL(info_teamleader.id, '') USING utf8) AS `Agent Info - Team Leader - System ID`,
IFNULL(info_teamleader.name_ldap, '') AS `Agent Info - Team Leader - Name`,
creator.id AS `Created By - System ID`,
creator.name AS `Created By - Name`,
CONVERT(IFNULL(authorizing_teamleader_user.id, '') USING utf8) AS `Processed By - Team Leader - System ID`,
IFNULL(authorizing_teamleader_user.name_ldap, '') AS `Processed By - Team Leader - Name`,
CONVERT(IFNULL(authorizing_teamleader_user.emp_id, '') USING utf8) AS `Processed By - Team Leader - Employee ID`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.datetime, '%d %b %Y'), '') USING utf8) AS `Processed On - Team Leader - Date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.datetime, '%h:%i %p'), '') USING utf8) AS `Processed On - Team Leader - Time`,
CONVERT(IFNULL(authorizing_manager_user.id, '') USING utf8) AS `Processed By - Manager - System ID`,
IFNULL(authorizing_manager_user.name_ldap, '') AS `Processed By - Manager - Name`,
CONVERT(IFNULL(authorizing_manager_user.emp_id, '') USING utf8) AS `Processed By - Manager - Employee ID`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.datetime, '%d %b %Y'), '') USING utf8) AS `Processed On - Manager - Date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.datetime, '%h:%i %p'), '') USING utf8) AS `Processed On - Manager - Time`,
CONVERT(IFNULL(authorizing_director_user.id, '') USING utf8) AS `Processed By - Director - System ID`,
IFNULL(authorizing_director_user.name_ldap, '') AS `Processed By - Director - Name`,
CONVERT(IFNULL(authorizing_director_user.emp_id, '') USING utf8) AS `Processed By - Director - Employee ID`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.datetime, '%d %b %Y'), '') USING utf8) AS `Processed On - Director - Date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.datetime, '%h:%i %p'), '') USING utf8) AS `Processed On - Director - Time`,
status.des AS `Status`,
CONVERT(IF(status.des = 'Pending', '', status_by.id) USING utf8) AS `Status By - System ID`,
IFNULL(status_by.name_ldap, '') AS `Status By - Name`,
CONVERT(IFNULL(status_by.emp_id, '') USING utf8) AS `Status By - Employee ID`,
IFNULL(status_by_role.des, '') AS `Status By - Position`,
CONVERT(IFNULL(DATE_FORMAT(form.statuson, '%d %b %Y'), '') USING utf8) AS `Status On - Date`,
CONVERT(IFNULL(DATE_FORMAT(form.statuson, '%h:%i %p'), '') USING utf8) AS `Status On - Time`,
CONCAT('http://cns/pdf/', form.pdf) AS `PDF`
FROM cns_form AS form
JOIN cns_departments AS department
ON form.deptid = department.id
JOIN cns_sections AS section
ON form.sectid = section.id
JOIN (cns_users AS reporting_to_1,
cns_departments AS reporting_to_1_department)
ON reporting_to_1.id = form.rprtid1
AND reporting_to_1.dept_id = reporting_to_1_department.id
LEFT JOIN (cns_users AS reporting_to_2,
cns_departments AS reporting_to_2_department)
ON reporting_to_2.id = form.rprtid2
AND reporting_to_2.dept_id = reporting_to_2_department.id
LEFT JOIN cns_form_attachments AS attachment_1
ON form.id = attachment_1.form_id
AND attachment_1.id = (SELECT MIN(id)
FROM cns_form_attachments
WHERE form_id = form.id)
LEFT JOIN cns_form_attachments AS attachment_2
ON form.id = attachment_2.form_id
AND attachment_2.id = (SELECT MAX(id)
FROM cns_form_attachments
WHERE form_id = form.id)
JOIN cns_status AS status
ON form.status = status.id
JOIN cns_form_type AS form_type
ON form.waivetype = form_type.id
LEFT JOIN (cns_form_infos AS info,
cns_users AS info_teamleader)
ON form.id = info.form_id
AND info_teamleader.id = info.teamleaderid
JOIN cns_users AS creator
ON creator.id = form.empid
LEFT JOIN (cns_form_authorizers AS authorizing_teamleader,
cns_users AS authorizing_teamleader_user)
ON authorizing_teamleader.form = form.id
AND authorizing_teamleader.role = 't'
AND authorizing_teamleader_user.id = authorizing_teamleader.`from`
LEFT JOIN (cns_form_authorizers AS authorizing_manager,
cns_users AS authorizing_manager_user)
ON authorizing_manager.form = form.id
AND authorizing_manager.role = 'm'
AND authorizing_manager_user.id = authorizing_manager.`from`
LEFT JOIN (cns_form_authorizers AS authorizing_director,
cns_users AS authorizing_director_user)
ON authorizing_director.form = form.id
AND authorizing_director.role = 'd'
AND authorizing_director_user.id = authorizing_director.`from`
LEFT JOIN (cns_users AS status_by,
cns_roles AS status_by_role)
ON status_by.id = form.statusby
AND status_by.role = status_by_role.id
WHERE status.des != 'Pending'
ORDER BY form.id DESC
Ran EXPLAIN on the above query
+----+--------------------+-----------------------------+--------+------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------------+--------+------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------------+
| 1 | PRIMARY | form | ALL | empid,rprtid1,deptid,sectid,status | NULL | NULL | NULL | 779 | Using temporary; Using filesort |
| 1 | PRIMARY | authorizing_teamleader | ref | from,role | role | 1 | const | 0 | Using where |
| 1 | PRIMARY | authorizing_teamleader_user | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.authorizing_teamleader.from | 1 | NULL |
| 1 | PRIMARY | authorizing_manager | ref | from,role | role | 1 | const | 0 | Using where |
| 1 | PRIMARY | authorizing_manager_user | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.authorizing_manager.from | 1 | NULL |
| 1 | PRIMARY | status | ALL | PRIMARY,des | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | reporting_to_1 | eq_ref | PRIMARY,dept_id | PRIMARY | 4 | wfs-test.form.rprtid1 | 1 | Using where |
| 1 | PRIMARY | reporting_to_2 | eq_ref | PRIMARY,dept_id | PRIMARY | 4 | wfs-test.form.rprtid2 | 1 | NULL |
| 1 | PRIMARY | reporting_to_2_department | ALL | PRIMARY | NULL | NULL | NULL | 9 | Using where |
| 1 | PRIMARY | info | eq_ref | form_id_UNIQUE | form_id_UNIQUE | 4 | wfs-test.form.id | 1 | NULL |
| 1 | PRIMARY | info_teamleader | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.info.teamleaderid | 1 | NULL |
| 1 | PRIMARY | creator | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.form.empid | 1 | NULL |
| 1 | PRIMARY | attachment_1 | ref | PRIMARY,form_id | form_id | 4 | wfs-test.form.id | 1 | Using where |
| 1 | PRIMARY | attachment_2 | ref | PRIMARY,form_id | form_id | 4 | wfs-test.form.id | 1 | Using where |
| 1 | PRIMARY | authorizing_director | ref | from,role | role | 1 | const | 0 | Using where |
| 1 | PRIMARY | authorizing_director_user | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.authorizing_director.from | 1 | NULL |
| 1 | PRIMARY | status_by | eq_ref | PRIMARY,role | PRIMARY | 4 | wfs-test.form.statusby | 1 | NULL |
| 1 | PRIMARY | status_by_role | ALL | PRIMARY | NULL | NULL | NULL | 8 | Using where |
| 1 | PRIMARY | department | ALL | PRIMARY | NULL | NULL | NULL | 9 | Using where; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | reporting_to_1_department | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.reporting_to_1.dept_id | 1 | NULL |
| 1 | PRIMARY | section | eq_ref | PRIMARY | PRIMARY | 4 | wfs-test.form.sectid | 1 | NULL |
| 3 | DEPENDENT SUBQUERY | cns_form_attachments | ref | form_id | form_id | 4 | wfs-test.form.id | 1 | NULL |
| 2 | DEPENDENT SUBQUERY | cns_form_attachments | ref | form_id | form_id | 4 | wfs-test.form.id | 1 | NULL |
+----+--------------------+-----------------------------+--------+------------------------------------+----------------+---------+--------------------------------------+------+----------------------------------------------------+
