Extremely Slow Select Query on MySQL

Question

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.

Answer 1

I can't add comments, so I'll throw my two cents in as an answer. This only applies if you don't own the server and are sharing it with other services. Laptop, single disk spinning with all records on the platter nice and neat. The DB is the only thing using resources.

Server, shared services, SAN or RAID disk, files aren't on a single platter, and you are competing for resources. If you have access, check resources like CPU utilization while you run your query. If you are sharing this server with others, chances are you've only been allocated so much processor resource and ram to perform your query. While your server specs are impressive, shared environments hand out a sliver of resources to each party.

In this case, your laptop is the more powerful device. Ask your sysadmin for more resources.

Answer 2

The main issue of your query is you used too many useless convert using utf-8 (for id and datetime???), just remove all of your convert, I can run your query in my laptop less than 1s (before removing, it takes more than 30s, and I don't have patient to wait more, 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 3

Things do absolutely not scale linear in a database. I once have altered a query that contained a lot of calculations by pre-selecting 7% of the original table with an additional clause in the where-part. The result was not an increase in speed of about 15 times, but more than 3000 times!

(I suspect the DBMS was al of the sudden able to completely store the table in memory)

Anyway; you could try using a profiler to see the actual query that is performed on both systems and the times the different steps take. If not mistaken there are profilers available for MySQL.

Answer 4

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.

Answer 5

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?