Improve MySQL query speed with lots of joins

Question

I have a query in MySQL which uses multiple joins and it runs slow at the moment - on average it is taking around 35 seconds to run.

The query is:

SELECT t.id,
       CASE t.emp_accepted
           WHEN '1' THEN 'No'
           WHEN '0' THEN 'Yes'
       END AS accepted,
       e.department,
       e.works_id,
       e.first_name,
       e.sur_name,
       e.job_title,
       e.job_status,
       e.site_id,
       e.manager,
       d1.department_name AS dept_name,
       d2.department_name AS sub_dept_name,
       temp_hours_worked.hours AS hours,
       s.office_name AS site_name,
       CONCAT(e2.first_name, ' ', e2.sur_name) AS manager_name,
       CONCAT(e3.first_name, ' ', e3.sur_name) AS validated_by
FROM time t
LEFT JOIN employee e
    ON t.employee_id = e.employee_id
LEFT JOIN departments d1
    ON e.department = d1.id
LEFT JOIN departments d2
    ON e.sub_department = d2.id
LEFT JOIN site s
    ON e.site_id = s.id
LEFT JOIN employee e2
    ON e.manager = e2.id
LEFT JOIN employee e3
    ON t.manager_id = e3.id
LEFT JOIN temp_hours_worked
    ON temp_hours_worked.week_beginning = t.week_beginning
        AND temp_hours_worked.employee_id = t.employee_id
        AND temp_hours_worked.company_id=?
WHERE t.company_id = ?;

Explain:

+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+
| id | select_type | table             | type   | possible_keys | key         | key_len | ref                                     | rows | Extra |
+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+
|  1 | SIMPLE      | t                 | ref    | company_id    | company_id  | 4       | const                                   | 5566 |       |
|  1 | SIMPLE      | e                 | ref    | employee_id   | employee_id | 4       | DBNAME.t.employee_id                    |    1 |       |
|  1 | SIMPLE      | d1                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.department                     |    1 |       |
|  1 | SIMPLE      | d2                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.sub_department                 |    1 |       |
|  1 | SIMPLE      | s                 | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.site_id                        |    1 |       |
|  1 | SIMPLE      | e2                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.manager                        |    1 |       |
|  1 | SIMPLE      | e3                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.t.manager_id                     |    1 |       |
|  1 | SIMPLE      | temp_hours_worked | ref    | company_id    | company_id  | 4       | const                                   | 5566 |       |
+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+

MySQL version is 5.5.31 running on Centos 6.5 64 bit and the server is 8 core, 4GB RAM with SSD disks. Load average on the box is:

load average: 0.24, 0.29, 0.29

and free memory shows as:

             total       used       free     shared    buffers     cached
Mem:          3880       3067        813          0        177       1065
-/+ buffers/cache:       1825       2055
Swap:         1023          0       1023

Disk space is OK:

Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       45G   11G   32G  26% /
tmpfs           1.9G     0  1.9G   0% /dev/shm
/usr/tmpDSK    1008M   51M  907M   6% /tmp

Output from hdparm -Tt /dev/xvda1

 Timing cached reads:   12538 MB in  1.99 seconds = 6297.76 MB/sec
 Timing buffered disk reads: 826 MB in  3.00 seconds = 275.27 MB/sec

my.cnf:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]


local-infile=0

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid_file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
#sql_mode                      = NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
#innodb_strict_mode            = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0

max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1456M

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
#log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log

The columns being queried/joined are all necessary and cannot be removed and I realise there is no index on quite a few of the columns but as they are only single rows I am not sure it matters - is there anything else I can do to speed this query up?

Improve MySQL query speed with lots of joins

Answers (1)

Related Questions