Menu
I’m trying to understand why a select query runs extremely fast on my laptop while extremely slow on the server. The query takes 1.388 seconds to run on the laptop while 49.670 seconds on the server. Both schemas are identical as I have exported the scheme from the laptop and imported it into the server. Both are running MySQL 5.1.36 on WAMP 2.0.
SQL Dump
https://db.tt/4TvuOWbD
Query
SELECT form.id AS 'Form ID',
DATE_FORMAT(form.created_on, '%d %b %Y') AS 'Created On - Date',
DATE_FORMAT(form.created_on, '%h:%i %p') AS 'Created On - Time',
department.name AS 'Department',
section.name AS 'Section',
reporting_to_1.id AS 'Reporting To 1 - System ID',
reporting_to_1.real_name AS 'Reporting To 1 - Name',
reporting_to_1_department.name AS 'Reporting To 1 - Department',
reporting_to_1_section.name AS 'Reporting To 1 - Section',
CONVERT(IFNULL(reporting_to_2.id, '') USING utf8) AS 'Reporting To 2 - System ID',
IFNULL(reporting_to_2.real_name, '') AS 'Reporting To 2 - Name',
IFNULL(reporting_to_2_department.name, '') AS 'Reporting To 2 - Department',
IFNULL(reporting_to_2_section.name, '') AS 'Reporting To 2 - Section',
form_type.type AS 'Form Type',
CONVERT(IF(form.customer_number = 0, '-', form.customer_number) USING utf8) AS 'Customer Number',
form.customer_name AS 'Customer Name',
form.customer_contract AS 'Customer Contract No.',
DATE_FORMAT(form.action_date, '%d %b %Y') AS 'Action - On Date',
CONCAT('http://cns', attachment_1.path, '/', attachment_1.filename_generated) AS 'Attachment - 1',
CONCAT('http://cns', attachment_2.path, '/', attachment_2.filename_generated) AS 'Attachment - 2',
agent.name AS 'Agent - Name',
agent.tag AS 'Agent - Tag',
agent.type AS 'Agent - Type',
CONVERT(IFNULL(agent_teamleader.real_name, '') USING utf8) AS 'Agent - Team Leader - Name',
creator.id AS `creator id`,
creator.real_name AS `creator full name`,
CONVERT(IFNULL(authorizing_teamleader_user.id, '') USING utf8) AS `processed by - team leader - system id`,
IFNULL(authorizing_teamleader_user.real_name, '') AS `processed by - team leader - name`,
CONVERT(IFNULL(authorizing_teamleader_user.employee_id, '') USING utf8) AS `processed by - team leader - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - team leader - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%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.real_name, '') AS `processed by - manager - name`,
CONVERT(IFNULL(authorizing_manager_user.employee_id, '') USING utf8) AS `processed by - manager - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - manager - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%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.real_name, '') AS `processed by - director - name`,
CONVERT(IFNULL(authorizing_director_user.employee_id, '') USING utf8) AS `processed by - director - employee id`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.action_date, '%d %b %Y'), '') USING utf8) AS `processed on - director - date`,
CONVERT(IFNULL(DATE_FORMAT(authorizing_director.action_date, '%h:%i %p'), '') USING utf8) AS `processed on - director - time`,
status.name AS `status`,
CONVERT(IF(status.name = 'Pending', '', user_status_by.id) USING utf8) AS `status by - system id`,
IFNULL(user_status_by.real_name, '') AS `status by - name`,
CONVERT(IFNULL(user_status_by.employee_id, '') USING utf8) AS `status by - employee id`,
IFNULL(user_status_by_role.name, '') AS `status by - position`,
CONVERT(IFNULL(DATE_FORMAT(form.status_on, '%d %b %Y'), '') USING utf8) AS `status on - date`,
CONVERT(IFNULL(DATE_FORMAT(form.status_on, '%h:%i %p'), '') USING utf8) AS `status on - time`,
CONCAT('http://cns/pdf/', form.pdf) AS `pdf`
FROM forms AS form
JOIN (sections AS section,
departments AS department)
ON form.section_id = section.id
AND section.department_id = department.id
JOIN (users AS reporting_to_1,
sections AS reporting_to_1_section,
departments AS reporting_to_1_department)
ON reporting_to_1.id = form.reporting_to_1
AND reporting_to_1.section_id = reporting_to_1_section.id
AND reporting_to_1_section.department_id = reporting_to_1_department.id
LEFT JOIN (users AS reporting_to_2, sections AS reporting_to_2_section,
departments AS reporting_to_2_department)
ON reporting_to_2.id = form.reporting_to_2
AND reporting_to_2.section_id = reporting_to_2_section.id
AND reporting_to_2_section.department_id = reporting_to_2_department.id
JOIN form_type
ON form.type = form_type.id
LEFT JOIN attachments AS attachment_1
ON form.id = attachment_1.form
AND attachment_1.id = ( SELECT min(id)
FROM attachments
WHERE form = form.id)
LEFT JOIN attachments AS attachment_2
ON form.id = attachment_2.form
AND attachment_2.id = ( SELECT max(id)
FROM attachments
WHERE form = form.id)
LEFT JOIN (agents AS agent,
users AS agent_teamleader,
branches AS branch)
ON form.id = agent.form_id
AND agent_teamleader.id = agent.teamleader_id
AND branch.id = agent.branch_id
JOIN users AS creator
ON form.user_id = creator.id
LEFT JOIN (authorizers AS authorizing_teamleader,
users AS authorizing_teamleader_user)
ON authorizing_teamleader.form_id = form.id
AND authorizing_teamleader_user.id = authorizing_teamleader.`from`
AND authorizing_teamleader_user.role = 't'
LEFT JOIN (authorizers AS authorizing_manager,
users AS authorizing_manager_user)
ON authorizing_manager.form_id = form.id
AND authorizing_manager_user.id = authorizing_manager.`from`
AND authorizing_manager_user.role = 'm'
LEFT JOIN (authorizers AS authorizing_director,
users AS authorizing_director_user)
ON authorizing_director.form_id = form.id
AND authorizing_director_user.id = authorizing_director.`from`
AND authorizing_director_user.role = 'd'
JOIN status
ON form.status = status.id
LEFT JOIN (users AS user_status_by,
roles AS user_status_by_role)
ON user_status_by.id = form.status_by_user_id
AND user_status_by_role.id = user_status_by.role
GROUP BY form.id
ORDER BY form.id DESC
LIMIT 0, 100
EXPLAIN EXTENDED - SERVER
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
| 1 | PRIMARY | section | ALL | PRIMARY,IDX_DEPARTMENT | | | | 18 | 100 | Using temporary; Using filesort |
| 1 | PRIMARY | department | eq_ref | PRIMARY | PRIMARY | 4 | cns.section.department_id | 1 | 100 | |
| 1 | PRIMARY | form | ref | IDX_USER_ID,IDX_REPORTING_TO_1,IDX_SECTION,IDX_TYPE,IDX_STATUS | IDX_SECTION | 4 | cns.section.id | 528 | 100 | |
| 1 | PRIMARY | status | eq_ref | PRIMARY | PRIMARY | 3 | cns.form.status | 1 | 100 | |
| 1 | PRIMARY | form_type | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.type | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_teamleader.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_manager.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_director | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_director_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_director.from | 1 | 100 | |
| 1 | PRIMARY | attachment_1 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | attachment_2 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | agent | ref | IDX_FORM_ID,IDX_BRANCH_ID,IDX_TEAMLEADER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | agent_teamleader | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.teamleader_id | 1 | 100 | |
| 1 | PRIMARY | branch | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.branch_id | 1 | 100 | Using index |
| 1 | PRIMARY | reporting_to_1 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_1 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_2 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_2.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_2_section.department_id | 1 | 100 | |
| 1 | PRIMARY | creator | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.user_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_1.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_1_section.department_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.form.status_by_user_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by_role | eq_ref | PRIMARY | PRIMARY | 3 | cns.user_status_by.role | 1 | 100 | |
| 3 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
| 2 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+-------------+---------+------------------------------------------+------+----------+---------------------------------+
EXPLAIN EXTENDED - LAPTOP
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | form_type | index | PRIMARY | IDX_FORM_TYPE | 137 | | 2 | 100 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | form | ref | IDX_USER_ID,IDX_REPORTING_TO_1,IDX_SECTION,IDX_TYPE,IDX_STATUS | IDX_TYPE | 4 | cns.form_type.id | 1443 | 100 | |
| 1 | PRIMARY | status | eq_ref | PRIMARY | PRIMARY | 3 | cns.form.status | 1 | 100 | |
| 1 | PRIMARY | section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.form.section_id | 1 | 100 | |
| 1 | PRIMARY | department | eq_ref | PRIMARY | PRIMARY | 4 | cns.section.department_id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_teamleader_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_teamleader.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_manager_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_manager.from | 1 | 100 | |
| 1 | PRIMARY | authorizing_director | ref | IDX_FORM_ID,IDX_FROM_USER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | authorizing_director_user | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.authorizing_director.from | 1 | 100 | |
| 1 | PRIMARY | attachment_1 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | attachment_2 | eq_ref | PRIMARY,IDX_FORM_ID | PRIMARY | 4 | func | 1 | 100 | |
| 1 | PRIMARY | agent | ref | IDX_FORM_ID,IDX_BRANCH_ID,IDX_TEAMLEADER_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | |
| 1 | PRIMARY | agent_teamleader | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.teamleader_id | 1 | 100 | |
| 1 | PRIMARY | branch | eq_ref | PRIMARY | PRIMARY | 4 | cns.agent.branch_id | 1 | 100 | Using index |
| 1 | PRIMARY | reporting_to_1 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_1 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2 | eq_ref | PRIMARY,IDX_SECTION | PRIMARY | 4 | cns.form.reporting_to_2 | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_2.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_2_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_2_section.department_id | 1 | 100 | |
| 1 | PRIMARY | creator | eq_ref | PRIMARY | PRIMARY | 4 | cns.form.user_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_section | eq_ref | PRIMARY,IDX_DEPARTMENT | PRIMARY | 4 | cns.reporting_to_1.section_id | 1 | 100 | |
| 1 | PRIMARY | reporting_to_1_department | eq_ref | PRIMARY | PRIMARY | 4 | cns.reporting_to_1_section.department_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by | eq_ref | PRIMARY,IDX_ROLE | PRIMARY | 4 | cns.form.status_by_user_id | 1 | 100 | |
| 1 | PRIMARY | user_status_by_role | eq_ref | PRIMARY | PRIMARY | 3 | cns.user_status_by.role | 1 | 100 | |
| 3 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
| 2 | DEPENDENT SUBQUERY | attachments | ref | IDX_FORM_ID | IDX_FORM_ID | 4 | cns.form.id | 1 | 100 | Using index |
+----+--------------------+-----------------------------+--------+----------------------------------------------------------------+---------------+---------+------------------------------------------+------+----------+----------------------------------------------+
Model
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
Troubleshooting
1. Changed the engines for all tables in both databases from InnoDB to MyISAM with optimization. It took 89.435 seconds to run on the server and 57.252 seconds on the laptop. Laptop is still faster yet, extremely slow when compared to 1.388 seconds query time using InnoDB engine.
770
Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.
The data (not the schema) differs between laptop and server?
The explain shows that the section table selects all rows on server, instead of just one like on the laptop.
Also, it shows Using temporary; Using filesort in the server: that might be the source of the problem.
182
Perhaps the indexes in your table are not getting rebuilt after transferring the database from one machine to the other (I have experienced this before). You have to manually tell MySQL to rebuild the index. If I recall correctly you could so with an OPTIMIZE query
OPTIMIZE TABLE your_table
Non-existence of an index can significantly slow your query, although the difference you are experiencing might be too big to be explained by this problem. Like in the previous comments, could you post your tables/queries?
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
