Extremely slow query selection in MySQL

Question

I am trying to understand why the select query is very fast on my laptop, although it is very slow on the server. The request takes 1.388 seconds to run on the laptop, and 49.670 seconds on the server. Both schemes are identical, since I exported the scheme from a laptop and imported it to the server. Both versions of MySQL 5.1.36 run 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: 8 GB

Server Features

Operating System: Microsoft Windows 2008 Standard SP2, Processor: Intel® Xeon® Processor X5570 (8 MB, 2.93 GHz, 6.40 GHz / s Intel® QPI), Memory: 4 GB

Troubleshooting

1. The mechanism for all tables in both databases from InnoDB to MyISAM with optimization has been changed. It took 89.435 seconds and 57.252 seconds on a laptop to run on the server. The laptop is still faster, very slow, compared to a wait time of 1.388 seconds using the InnoDB engine.

Answer 1

Is the data (not the diagram) different between the laptop and the server?

The explanation shows that the section table selects all the rows on the server, and not just one, as on a laptop.

In addition, Using temporary; Using filesort displayed on the server Using temporary; Using filesort Using temporary; Using filesort : this may be the source of the problem.

Answer 2

Perhaps the indexes in your table will not be restored after moving the database from one machine to another (I have experienced this before). You need to manually inform MySQL about the index update. If I remember correctly, you could request OPTIMIZE

TABLE OPTIMIZATION your_table

The lack of an index can slow down your query significantly, although the difference you are experiencing may be too large to be explained by this problem. As in previous comments, can you post your tables / queries?

Answer 3

Things absolutely don't scale linearly in the database. I somehow changed the query, which contained a lot of calculations, pre-selecting 7% of the original table with an additional sentence in the part somewhere. As a result, there was no speed increase of about 15 times, but more than 3,000 times!

(I suspect that the DBMS was able to suddenly completely save the table in memory)

Anyway; you can try to use the profiler to see the actual request that runs on both systems, and the time that takes the various steps. If I am not mistaken, profilers are available for MySQL.

Answer 4

The main problem of your request is that you used too many useless convert using utf-8 (for id and datetime ???), just delete all your envelopes, I can run your request on my laptop for less than 1 s (before deleting, it takes more than 30 seconds and i have no patient wait any longer, just stop it)

 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', IFNULL(reporting_to_2.id, '') 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', IF(form.customer_number = 0, '-', form.customer_number) 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', IFNULL(agent_teamleader.real_name, '') AS 'Agent - Team Leader - Name', creator.id AS `creator id`, creator.real_name AS `creator full name`, IFNULL(authorizing_teamleader_user.id, '') AS `processed by - team leader - system id`, IFNULL(authorizing_teamleader_user.real_name, '') AS `processed by - team leader - name`, IFNULL(authorizing_teamleader_user.employee_id, '') AS `processed by - team leader - employee id`, IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%d %b %Y'), '') AS `processed on - team leader - date`, IFNULL(DATE_FORMAT(authorizing_teamleader.action_date, '%h:%i %p'), '') AS `processed on - team leader - time`, IFNULL(authorizing_manager_user.id, '') AS `processed by - manager - system id`, IFNULL(authorizing_manager_user.real_name, '') AS `processed by - manager - name`, IFNULL(authorizing_manager_user.employee_id, '') AS `processed by - manager - employee id`, IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%d %b %Y'), '') AS `processed on - manager - date`, IFNULL(DATE_FORMAT(authorizing_manager.action_date, '%h:%i %p'), '') AS `processed on - manager - time`, IFNULL(authorizing_director_user.id, '') AS `processed by - director - system id`, IFNULL(authorizing_director_user.real_name, '') AS `processed by - director - name`, IFNULL(authorizing_director_user.employee_id, '') AS `processed by - director - employee id`, IFNULL(DATE_FORMAT(authorizing_director.action_date, '%d %b %Y'), '') AS `processed on - director - date`, IFNULL(DATE_FORMAT(authorizing_director.action_date, '%h:%i %p'), '') AS `processed on - director - time`, status.name AS `status`, IF(status.name = 'Pending', '', user_status_by.id) AS `status by - system id`, IFNULL(user_status_by.real_name, '') AS `status by - name`, IFNULL(user_status_by.employee_id, '') AS `status by - employee id`, IFNULL(user_status_by_role.name, '') AS `status by - position`, IFNULL(DATE_FORMAT(form.status_on, '%d %b %Y'), '') AS `status on - date`, IFNULL(DATE_FORMAT(form.status_on, '%h:%i %p'), '') 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 

Answer 5

I can not add comments, so I will throw my two cents as an answer. This only applies if you do not own the server and share it with other services. A laptop, a single disc spinning with all the records on the dish, nice and neat. A database is the only one that uses resources.

Server, shared services, SAN or RAID, files are not on the same drive, and you are competing for resources. If you have access, check resources such as CPU utilization when you run the request. If you share this server with others, most likely you have been allocated so many processor and ram resources to fulfill your request. While your server features are impressive, shared environments provide a feed of resources for each side.

In this case, your laptop is a more powerful device. Assign additional resources to your system administrator.