SlowQueryとcreate table

slow.sql

# Time: 150513  9:34:49
# User@Host: myfleet[myfleet] @ localhost [127.0.0.1]
# Thread_id: 63  Schema: myfleet  QC_hit: No
# Query_time: 8.689849  Lock_time: 0.000136  Rows_sent: 2096  Rows_examined: 3635960
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
SET timestamp=1431477289;
select br.area_id, br.info_no, br.cell, br.win_rank, ci.alphabet, count(1) as cnt from battle_result br left join cell_info ci on  br.area_id = ci.area_id and br.info_no = ci.info_no and br.cell = ci.cell  where  br.created > 1396278000000 and  br.created < 1431442800000  group by br.area_id, br.info_no, br.cell, br.win_rank  order by br.area_id, br.info_no, br.cell, br.win_rank;

# 以下のように書き換えたら3倍ぐらい早くなった
select br.area_id, br.info_no, br.cell, br.win_rank, ci.alphabet, br.cnt from (select area_id, info_no, cell, win_rank, count(*) as cnt from battle_result where created > 1396278000000 and created < 1431442800000 group by area_id, info_no, cell, win_rank) br left join cell_info ci on br.area_id = ci.area_id and br.info_no = ci.info_no and br.cell = ci.cell;

Create Table: CREATE TABLE `battle_result` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `member_id` bigint(20) NOT NULL,
  `area_id` tinyint(4) NOT NULL,
  `info_no` tinyint(4) NOT NULL,
  `cell` tinyint(4) NOT NULL,
  `enemies` tinytext NOT NULL,
  `win_rank` char(1) NOT NULL,
  `quest_name` tinytext NOT NULL,
  `quest_level` tinyint(4) NOT NULL,
  `enemy_deck` tinytext NOT NULL,
  `first_clear` tinyint(1) NOT NULL,
  `get_ship_id` int(11) DEFAULT NULL,
  `get_ship_type` tinytext,
  `get_ship_name` tinytext,
  `created` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `br_area_info_cell_win` (`area_id`,`info_no`,`cell`,`win_rank`),
  KEY `battle_result_get_ship` (`get_ship_id`),
  KEY `battle_result_created` (`created`),
  KEY `battle_result_member_id` (`member_id`),
  KEY `battle_result_win_rank` (`win_rank`)
) ENGINE=Aria AUTO_INCREMENT=1817944 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1