|
EDIT 5:
Looks like I may have been mistaken and posted prematurely. When I test now, even the mysql.exe command hangs on some queries. Haven't been able to find a workaround...
EDIT 4:
Further investigation shows that this issue occurs when querying through PHP mysqli, and not when querying through the native mysql.exe command. However, so far, this issue has not occurred when using Oracle MySQL...
OP:
Some queries randomly hang, and once they do, the problem continuously persists across restarts of the service. Query Cache seems to be disabled on this particular configuration, and I tried commenting out most config settings, but the issue recurs. The issue is temporarily fixed by deleting all the tables, recreating, then reinserting data to tables, but the issue returns randomly and frequently. When one query is hung, some other similar queries still execute fine.
Example DB Script is attached. Table names have been changed from original. Can't provide data.
Example query (constant numeric values have been changed from original):
SELECT n.code ca, k.code cb, h.code cc, e.code cd, va.val va, vb.val vb, vc.val vc, vd.val vd, ve.val ve, vf.val vf, vg.val vg, vh.val vh, vi.val vi, vj.val vj, vk.val vk, vl.val vl, vm.val vm, vn.val vn, vo.val vo, vp.val vp, vq.val vq, vr.val vr, vs.val vs, vt.val vt, vu.val vu, vv.val vv, vw.val vw, vx.val vx FROM (SELECT id FROM td_v WHERE `table` = "B" AND `date` <= '2018-01-01' ORDER BY `date` DESC LIMIT 1) a JOIN (SELECT id, td_v, tc_h FROM td_m) b ON td_v = a.id JOIN (SELECT id, parent, tc_v FROM tc_h) c ON c.id = tc_h JOIN (SELECT id, tc_m FROM tc_v) d ON d.id = c.tc_v JOIN (SELECT id, code FROM tc_m) e ON e.id = d.tc_m JOIN (SELECT id, parent, tc_v FROM tc_h) f ON f.id = c.parent JOIN (SELECT id, tc_m FROM tc_v) g ON g.id = f.tc_v JOIN (SELECT id, code FROM tc_m) h ON h.id = g.tc_m JOIN (SELECT id, parent, tc_v FROM tc_h) i ON i.id = f.parent JOIN (SELECT id, tc_m FROM tc_v) j ON j.id = i.tc_v JOIN (SELECT id, code FROM tc_m) k ON k.id = j.tc_m JOIN (SELECT id, parent, tc_v FROM tc_h) l ON l.id = i.parent JOIN (SELECT id, tc_m FROM tc_v) m ON m.id = l.tc_v JOIN (SELECT id, code FROM tc_m) n ON n.id = m.tc_m JOIN (SELECT td_m, val FROM td_i WHERE tb_h = 1) va ON va.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 2) vb ON vb.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 3) vc ON vc.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 4) vd ON vd.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 5) ve ON ve.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 6) vf ON vf.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 7) vg ON vg.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 8) vh ON vh.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 9) vi ON vi.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 10) vj ON vj.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 11) vk ON vk.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 12) vl ON vl.td_m = b.id JOIN (SELECT td_m, val FROM td_t WHERE tb_h = 13) vm ON vm.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 14) vn ON vn.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 15) vo ON vo.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 16) vp ON vp.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 17) vq ON vq.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 18) vr ON vr.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 19) vs ON vs.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 20) vt ON vt.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 21) vu ON vu.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 22) vv ON vv.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 23) vw ON vw.td_m = b.id JOIN (SELECT td_m, val FROM td_n WHERE tb_h = 24) vx ON vx.td_m = b.id ORDER BY b.id
|
|
EDIT 1:
When hung EXPLAIN hangs as well. When not hung (tables and indexes have been renamed from original):
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY ALL 2 Using temporary; Using filesort
|
1 PRIMARY td_m ref PRIMARY,UQ_TD_M UQ_TD_M 4 a.id 1 Using index
|
1 PRIMARY td_i eq_ref PRIMARY,IX_TD_I PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY tc_h eq_ref PRIMARY,UQ_TC_H,IX_TC_H PRIMARY 8 db.td_m.tc_h 1
|
1 PRIMARY tc_v eq_ref PRIMARY,IX_TC_V PRIMARY 8 db.tc_h.tc_v 1
|
1 PRIMARY tc_m eq_ref PRIMARY PRIMARY 4 db.tc_v.tc_m 1
|
1 PRIMARY tc_h eq_ref PRIMARY,UQ_TC_H,IX_TC_H PRIMARY 8 db.tc_h.parent 1
|
1 PRIMARY tc_v eq_ref PRIMARY,IX_TC_V PRIMARY 8 db.tc_h.tc_v 1
|
1 PRIMARY tc_m eq_ref PRIMARY PRIMARY 4 db.tc_v.tc_m 1
|
1 PRIMARY tc_h eq_ref PRIMARY,UQ_TC_H,IX_TC_H PRIMARY 8 db.tc_h.parent 1
|
1 PRIMARY tc_v eq_ref PRIMARY,IX_TC_V PRIMARY 8 db.tc_h.tc_v 1
|
1 PRIMARY tc_m eq_ref PRIMARY PRIMARY 4 db.tc_v.tc_m 1
|
1 PRIMARY tc_h eq_ref PRIMARY,IX_TC_H PRIMARY 8 db.tc_h.parent 1
|
1 PRIMARY tc_v eq_ref PRIMARY,IX_TC_V PRIMARY 8 db.tc_h.tc_v 1
|
1 PRIMARY tc_m eq_ref PRIMARY PRIMARY 4 db.tc_v.tc_m 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_n eq_ref PRIMARY,IX_TD_N PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
1 PRIMARY td_t eq_ref PRIMARY,IX_TD_T PRIMARY 12 db.td_m.id,const 1
|
2 DERIVED td_v ref UQ_TD_V UQ_TD_V 1 const 1 Using where; Using index
|
|
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "td_t",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "IX_TD_T",
|
"key_length": "4",
|
"used_key_parts": ["tb_h"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "td_t.tb_h <=> 2"
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TD_M"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.td_t.td_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_I"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_t.td_m", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.td_m.tc_h"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.td_m.td_v"],
|
"rows": 2,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "td_v",
|
"access_type": "ref",
|
"possible_keys": ["UQ_TD_V"],
|
"key": "UQ_TD_V",
|
"key_length": "1",
|
"used_key_parts": ["table"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "td_v.`table` <=> 'B' and td_v.`table` = 'B' and td_v.`date` <= '2018-01-01'",
|
"using_index": true
|
}
|
}
|
}
|
}
|
}
|
}
|
|
EDIT 2:
Note: The queries hang without any known changes being made to data.
ANALYZE TABLE when hung:
Table Op Msg_type Msg_text
|
fc.td_v analyze status OK
|
fc.td_m analyze status OK
|
fc.tc_h analyze status OK
|
fc.tc_v analyze status OK
|
fc.tc_m analyze status OK
|
fc.td_i analyze status OK
|
fc.td_t analyze status OK
|
fc.td_n analyze status OK
|
|
EDIT 3:
I realized after posting that since the query I posted has altered constants, it wasn't relating to the original data in the EXPLAIN, so here is an updated EXPLAIN going back to the original query. There should be a total of 35296 result rows:
{
|
"query_block": {
|
"select_id": 1,
|
"filesort": {
|
"sort_key": "td_m.`id`",
|
"temporary_table": {
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "td_v",
|
"access_type": "ref",
|
"possible_keys": ["UQ_TD_V"],
|
"key": "UQ_TD_V",
|
"key_length": "1",
|
"used_key_parts": ["table"],
|
"ref": ["const"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "td_v.`table` <=> 'B' and td_v.`table` = 'B' and td_v.`date` <= '2018-01-04'",
|
"using_index": true
|
}
|
}
|
}
|
},
|
"table": {
|
"table_name": "td_m",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "UQ_TD_M"],
|
"key": "UQ_TD_M",
|
"key_length": "4",
|
"used_key_parts": ["td_v"],
|
"ref": ["a.id"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "td_i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_I"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.td_m.tc_h"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"rows": 1,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
|
And here is an ANALYZE before hang:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 939.09,
|
"filesort": {
|
"sort_key": "td_m.`id`",
|
"r_loops": 1,
|
"r_total_time_ms": 7.4543,
|
"r_used_priority_queue": false,
|
"r_output_rows": 35296,
|
"r_buffer_size": "793Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0188,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.0465,
|
"table": {
|
"table_name": "td_v",
|
"access_type": "ref",
|
"possible_keys": ["UQ_TD_V"],
|
"key": "UQ_TD_V",
|
"key_length": "1",
|
"used_key_parts": ["table"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0136,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "td_v.`table` <=> 'B' and td_v.`table` = 'B' and td_v.`date` <= '2018-01-04'",
|
"using_index": true
|
}
|
}
|
}
|
},
|
"table": {
|
"table_name": "td_m",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "UQ_TD_M"],
|
"key": "UQ_TD_M",
|
"key_length": "4",
|
"used_key_parts": ["td_v"],
|
"ref": ["a.id"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 35296,
|
"r_total_time_ms": 8.3094,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "td_i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_I"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 29.457,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.td_m.tc_h"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 22.337,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 21.693,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 26.549,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0607,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0508,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0683,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "UQ_TC_H", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0405,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0297,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0337,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_h",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_H"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.parent"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0281,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_v",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TC_V"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_h.tc_v"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0271,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "tc_m",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["db.tc_v.tc_m"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0269,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 30.967,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 26.828,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 27.395,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.523,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.462,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.875,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 27.017,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 26.335,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.547,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 26.584,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.332,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.075,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 24.841,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.051,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_n",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_N"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 25.253,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 26.361,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 24.559,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 24.193,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 24.659,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 22.76,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 23.764,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 23.156,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "td_t",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "IX_TD_T"],
|
"key": "PRIMARY",
|
"key_length": "12",
|
"used_key_parts": ["td_m", "tb_h"],
|
"ref": ["db.td_m.id", "const"],
|
"r_loops": 35296,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 23.379,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
}
|
}
|
|
|
RECREATE.SQL