MySQL Query Optimization for JOIN Large Tables

Question

I have a problem with my MySQL query with large data access, When the query optimized with join it gives the output within 122 seconds for the data of one week. Then for one month data it takes 526 seconds for the process. I want to optimize this query for less amount of process time per year or if there any way to optimize MySQL settings in general ?

Table details. I refer two tables which mdiaries and tv_diaries,In both tables I have indexed relevant columns, In mdiaries table there are 2661331 rows and 27074645 rows in tv_diaries.

mdiaries table:

  INDEX area (area),
  INDEX date (date),
  INDEX district (district),
  INDEX gaDivision (gaDivision),
  INDEX member_id (member_id),
  INDEX tv_channel_id (tv_channel_id),

tv_diaries.

  INDEX area (area),
  INDEX date (date),
  INDEX district (district),
  INDEX member_id (member_id),
  INDEX timeslot_id (timeslot_id),
  INDEX tv_channel_id (tv_channel_id),

This is my query which takes 122 seconds to execute.

$sql = "SELECT COUNT(TvDiary.id) AS m_count,TvDiary.date,TvDiary.timeslot_id,TvDiary.tv_channel_id,TvDiary.district,TvDiary.area
FROM `mdiaries` AS Mdiary INNER JOIN `tv_diaries` AS TvDiary ON Mdiary.member_id = TvDiary.member_id
WHERE Mdiary.date >= '2014-01-01' AND Mdiary.date <= '2014-01-07'
AND TvDiary.date >= '2014-01-01' AND TvDiary.date <= '2014-01-07'
GROUP BY TvDiary.date,
TvDiary.timeslot_id,
TvDiary.tv_channel_id,
TvDiary.district,
TvDiary.area";

This is my.cnf file.

    [mysqld]

## General
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode                        = NO_ENGINE_SUBSTITUTION
#event-scheduler                = 1

## Networking
back-log                        = 100
#max-connections                = 200
max-connect-errors              = 10000
max-allowed-packet              = 32M
interactive-timeout             = 3600
wait-timeout                    = 600

### Storage Engines
#default-storage-engine         = InnoDB
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M

## InnoDB
innodb-buffer-pool-size        = 16G
innodb_buffer_pool_instances    = 16
#innodb-log-file-size           = 100M
#innodb-log-buffer-size         = 8M
#innodb-file-per-table          = 1
#innodb-open-files              = 300

## Replication
server-id                       = 1
#log-bin                        = /var/log/mysql/bin-log
#relay-log                      = /var/log/mysql/relay-log
relay-log-space-limit           = 16G
expire-logs-days                = 7
#read-only                      = 1
#sync-binlog                    = 1
#log-slave-updates              = 1
#binlog-format                  = STATEMENT
#auto-increment-offset          = 1
#auto-increment-increment       = 2

## Logging
log-output                      = FILE
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/slow-log
#log-slow-slave-statements
long-query-time                 = 2

##
query_cache_size        = 512M
query_cache_type        = 1
query_cache_limit       = 2M
join_buffer_size        = 512M
thread_cache_size       = 128

[mysqld_safe]
log-error                       = /var/log/mysqld.log
open-files-limit                = 65535

[mysql]
no-auto-rehash

Answer 1

Try adding multiple-columns index on all columns referenced in GROUP BY clause, as mentioned in the documentation.

INDEX grp (date, timeslot_id, tv_channel_id, district, area)

Answer 2

This is your query:

SELECT COUNT(t.id) AS m_count, t.date, t.timeslot_id, t.tv_channel_id,
       t.district, t.area
FROM `mdiaries` m INNER JOIN
     `tv_diaries` t
     ON m.member_id = t.member_id
WHERE m.date >= '2014-01-01' AND m.date <= '2014-01-07' AND
      t.date >= '2014-01-01' AND t.date <= '2014-01-07'
GROUP BY t.date, t.timeslot_id, t.tv_channel_id, t.district, t.area;

I would start with composite indexes: tv_diaries(date, member_id) and mdiaries(member_id, date).

This query is problematic, but these might help.