8

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

CNS 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.

AlGallaf
  • 627
  • 6
  • 15
  • 28
  • There is a big difference from the server explain and notebooks explain: On the notebook it is `Using Index` on ther section table and on the server it is not. So your problem lies on that missing index. – Jorge Campos Jan 26 '15 at 04:50
  • @JorgeCampos I ran `SHOW INDEX` on all tables on both the laptop and server. All data looks identical except for **Cardinality**. [Screenshots](http://imgur.com/a/yCk8R) – AlGallaf Jan 26 '15 at 06:54
  • Assuming you are still using InnDB,run `show variables like 'innodb_%';`.What is the value of innodb_buffer_pool_size ?Is it the same on both? – Mihai Jan 26 '15 at 09:59
  • @Mihai Same on both :( – AlGallaf Jan 26 '15 at 10:41
  • On the server optimize your tables `mysqlcheck -u youruser -p -o cns` – Mihai Jan 26 '15 at 11:14
  • @Mihai I'm getting the following message _note :Tables does not support optimize, doing recreate + analyze instead. status: OK_ [Screenshot](http://i.imgur.com/iexzDbT.png) – AlGallaf Jan 26 '15 at 11:56
  • Run the query again?Any improvements? – Mihai Jan 26 '15 at 11:58
  • @Mihai It's taking `299.350` seconds! [Screenshot](http://i.imgur.com/XGM3G5P.png) – AlGallaf Jan 26 '15 at 12:14
  • IDX_FORM_TYPE missing from table Form in production? It seems like it... – Ricardo C Jan 26 '15 at 17:45
  • Whats the value of the variable `optimizer_search_depth` on both servers – exussum Jan 27 '15 at 16:58
  • The explains are totally different (look at the tables mentioned in first four rows). This indicates that the data is different. And yes, it does make difference. – Salman A Jan 28 '15 at 09:25

5 Answers5

4

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?

lordphnx
  • 71
  • 7
  • check also the database engime – SQL.injection Jan 14 '15 at 08:55
  • But I have exported to my laptop and it's faster on it. Both DBs are running on MyISAM Engine. – AlGallaf Jan 14 '15 at 09:00
  • 1
    can you verify (e.g. in PhpMyAdmin) the Cardinality of your index? (i.e. the size) If those are 0 on your server machine, than that's the problem. – lordphnx Jan 14 '15 at 09:12
  • 1
    As I suspected, you are doing a lot of JOINs, which are very susceptible to not having a good indexing on any your tables. – lordphnx Jan 14 '15 at 09:14
  • @lordphnx Using MySQL Workbench, how can I verify the cardinality of my indexes? – AlGallaf Jan 14 '15 at 09:31
  • 1
    I barely ever use MySQL Workbench, but I found this: http://dev.mysql.com/doc/refman/5.0/en/show-index.html – lordphnx Jan 14 '15 at 09:37
  • @lordphnx All indexes have cardinality of +1 – AlGallaf Jan 14 '15 at 09:41
  • 1
    That should definitely be more, did you do the optimize queries? – lordphnx Jan 14 '15 at 09:46
  • @lordphnx I ran OPTIMIZE TABLE on all tables and I'm geting the following message `Table is already up to date`. Also, I ran SHOW INDEXES on all tables and here are the results: http://imgur.com/a/iqgXZ – AlGallaf Jan 14 '15 at 09:56
  • 1
    Ah, you get sizable cardinalities (I thought you meant they all equaled 1). In MySQL workbench you can view what the server is doing real-time if I remember correctly. Can you already see which activity it is? I once had a long query-time, and it turned out it was just taking a long time sending the results back to my client. – lordphnx Jan 14 '15 at 10:00
  • @lordphnx THANKS! The query now takes only `3.620` seconds!! I believe running `OPTIMIZE TABLE` on all tables helped. Is it possible to lower down the time? – AlGallaf Jan 14 '15 at 10:09
  • This did it for me. Queries taking so long the CPU got maxed out slowing down the whole system. The query just didn't use the index after upgrading to from 5.7 to 8. – posixpascal Dec 05 '22 at 11:04
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.

Alessandro Lai
  • 2,254
  • 2
  • 24
  • 32
  • I’m not sure why the data is not matching since I just exported/imported the data. Here are screenshots of tables’ information using MySQL Workbench: [Laptop](http://i.imgur.com/oaYmOfb.png) vs [Server](http://i.imgur.com/9LhUKmw.png) – AlGallaf Jan 20 '15 at 09:14
  • 1
    So much differences!! You have at least half the users, 50 agent rows and 300 form rows missing! – Alessandro Lai Jan 20 '15 at 09:16
  • You're right but would that really make that much of a difference in the query time? – AlGallaf Jan 20 '15 at 09:18
  • Maybe... But you can't make an onest comparison using different data. – Alessandro Lai Jan 20 '15 at 09:19
  • Strange! Every time I click on the `Refresh` button, I’m getting different data results from the server. Please look at the screenshots: [1](http://i.imgur.com/UoffekW.png) & [2](http://i.imgur.com/YMVPjDQ.png) – AlGallaf Jan 20 '15 at 09:28
  • 4
    For InnoDB tables, the server cannot tell the exact number of rows in the table other than counting them. It estimates the number (using various information it has), that's why the numbers change on each request. This is normal behaviour. – axiac Jan 20 '15 at 12:54
  • Try to execute ATANLYZE TABLE right before executing your query. – user1209304 Jan 22 '15 at 09:00
  • @user1209304 I ran `ANALYZE TABLE agents, attachments, authorizers, branches, departments, form_type, forms, roles, sections, status, users;`, and I got the following message `Table is already up to date`. [Screenshot](http://i.imgur.com/balGBqC.png) – AlGallaf Jan 22 '15 at 10:51
  • 1
    Could we see the my.cnf files for the laptop and servers? If the innodb_buffer_pool_size or tmp_table_size differs between the two, that might trigger the use of a temp file rather than keeping things in memory. – schtever Jan 24 '15 at 20:37
  • @schtever Server _my.ini_ file has _event_scheduler=ON_ at the end. Would this have any impact? – AlGallaf Jan 26 '15 at 07:05
1

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.

incomudro
  • 548
  • 4
  • 12
  • Both query profiles: [Laptop](https://db.tt/69cIbtJT) vs [Server](https://db.tt/6jfZH3Y0). Can you please help analyze the data? – AlGallaf Jan 20 '15 at 11:39
  • In server profile data "coping to tmp table" is much slower. – Mikrobi Jan 20 '15 at 11:55
  • Why is this issue occurring and how can I fix it? – AlGallaf Jan 20 '15 at 11:57
  • Hard to say. Maybe antivirus software (try disable realtime protection) ? – Mikrobi Jan 20 '15 at 12:08
  • No antivirus software is running on the server. Moreover, here a screenshot of the Windows [Task Manager Performance Tab](http://i.imgur.com/nsbD4dB.png). Would this activity impact the query time? – AlGallaf Jan 20 '15 at 12:15
  • CPU in your server is doing nothing :) , maybe give more memory to MySQL innodb engine [link] (http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html). And check write cache on server [link] (http://blogs.technet.com/b/askperf/archive/2010/11/05/performance-tuning-windows-server-2008-r2-pt-2.aspx). But safety first - make backup of your data, and don't check ‘Turn off Windows write-cache buffer flushing on the device’ before you sure what you will do. – Mikrobi Jan 20 '15 at 12:32
  • Both databases have the exact settings, I ran the following query `SELECT * FROM information_schema.global_variables WHERE variable_name LIKE 'innodb%';`. Would giving extra memory make a difference if the database on the laptop is working fine with the same memory size? – AlGallaf Jan 20 '15 at 12:43
  • "Removing tmp table" and "Opening data" takes about 60 times more time on the server, but although the difference is great, its impact on the total query result is little. The third process that takes a lot more time is "sending data". On the server it takes about 8 times more time. And as both on the laptop as well as on the server, the query seems to be "sending data" about 95% of the time, this seems to have the most impact on the performance and explains the difference you noticed in the end result. I have no experience with MySQL, but are you able to capture the query path? – incomudro Jan 20 '15 at 13:22
  • How can I capture the query path? – AlGallaf Jan 20 '15 at 13:28
  • I have little experience with MySQL. But perhaps this link provides enough information: http://dev.mysql.com/doc/refman/5.0/en/execution-plan-information.html – incomudro Jan 20 '15 at 14:20
1

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
Hieu Vo
  • 3,105
  • 30
  • 31
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.

Carl
  • 36
  • 6