StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query performance tuning about financial year
text
Body
copied!<p>I have a large table with around a 20 million records. the structure of the table with its indexes is:</p> <pre><code>CREATE TABLE `fact_ra` ( `replacement_record_id` varchar(45) NOT NULL, `product_tree_1` varchar(45) NOT NULL, `product_tree_2` varchar(45) NOT NULL, `product_tree_3` varchar(45) NOT NULL, `product_tree_4` varchar(45) NOT NULL, `claim_owner` varchar(255) DEFAULT NULL, `product_date` date DEFAULT NULL, `month` date DEFAULT NULL, `m1` decimal(10,0) DEFAULT NULL, `m2` decimal(10,0) DEFAULT NULL, `m3` decimal(10,0) DEFAULT NULL, `m4` decimal(10,0) DEFAULT NULL, `m5` decimal(10,0) DEFAULT NULL, `m6` decimal(10,0) DEFAULT NULL, `m7` decimal(10,0) DEFAULT NULL, `m8` decimal(10,0) DEFAULT NULL, `m9` decimal(10,0) DEFAULT NULL, `m10` decimal(10,0) DEFAULT NULL, `m11` decimal(10,0) DEFAULT NULL, `m12` decimal(10,0) DEFAULT NULL, `m13` decimal(10,0) DEFAULT NULL, `m14` decimal(10,0) DEFAULT NULL, `m15` decimal(10,0) DEFAULT NULL, `m16` decimal(10,0) DEFAULT NULL, `m17` decimal(10,0) DEFAULT NULL, `m18` decimal(10,0) DEFAULT NULL, `m19` decimal(10,0) DEFAULT NULL, `m20` decimal(10,0) DEFAULT NULL, `m21` decimal(10,0) DEFAULT NULL, `m22` decimal(10,0) DEFAULT NULL, `m23` decimal(10,0) DEFAULT NULL, `m24` decimal(10,0) DEFAULT NULL, `m25` decimal(10,0) DEFAULT NULL, `m26` decimal(10,0) DEFAULT NULL, `m27` decimal(10,0) DEFAULT NULL, `m28` decimal(10,0) DEFAULT NULL, `m29` decimal(10,0) DEFAULT NULL, `m30` decimal(10,0) DEFAULT NULL, `m31` decimal(10,0) DEFAULT NULL, `m32` decimal(10,0) DEFAULT NULL, `m33` decimal(10,0) DEFAULT NULL, `m34` decimal(10,0) DEFAULT NULL, `m35` decimal(10,0) DEFAULT NULL, `m36` decimal(10,0) DEFAULT NULL, `m37` decimal(10,0) DEFAULT NULL, `m38` decimal(10,0) DEFAULT NULL, `m39` decimal(10,0) DEFAULT NULL, `m40` decimal(10,0) DEFAULT NULL, `m41` decimal(10,0) DEFAULT NULL, `m42` decimal(10,0) DEFAULT NULL, `m43` decimal(10,0) DEFAULT NULL, `m44` decimal(10,0) DEFAULT NULL, `m45` decimal(10,0) DEFAULT NULL, `m46` decimal(10,0) DEFAULT NULL, `m47` decimal(10,0) DEFAULT NULL, `m48` decimal(10,0) DEFAULT NULL, `m49` decimal(10,0) DEFAULT NULL, `m50` decimal(10,0) DEFAULT NULL, `m51` decimal(10,0) DEFAULT NULL, `m52` decimal(10,0) DEFAULT NULL, `m53` decimal(10,0) DEFAULT NULL, `m54` decimal(10,0) DEFAULT NULL, `m55` decimal(10,0) DEFAULT NULL, `m56` decimal(10,0) DEFAULT NULL, `m57` decimal(10,0) DEFAULT NULL, `m58` decimal(10,0) DEFAULT NULL, `m59` decimal(10,0) DEFAULT NULL, `m60` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`replacement_record_id`), KEY `idx_product_tree_1` (`product_tree_1`), KEY `idx_product_tree_2` (`product_tree_2`), KEY `idx_product_tree_3` (`product_tree_3`), KEY `idx_product_tree_4` (`product_tree_4`), KEY `idx_month` (`month`), FULLTEXT KEY `fidx_claim_owner` (`claim_owner`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | </code></pre> <p>and I am runing the following query:</p> <pre><code>select case when month(product_date) >= 4 then concat(year(product_date), \'-\', year(product_date)+1) else concat(year(product_date)-1,\'-\', year(product_date)) end as financial_year, sum(t1.m1) as m1, sum(t1.m2) as m2, sum(t1.m3) as m3, sum(t1.m4) as m4, sum(t1.m5) as m5, sum(t1.m6) as m6, sum(t1.m7) as m7, sum(t1.m8) as m8, sum(t1.m9) as m9, sum(t1.m10) as m10, sum(t1.m11) as m11, sum(t1.m12) as m12, sum(t1.m13) as m13, sum(t1.m14) as m14, sum(t1.m15) as m15, sum(t1.m16) as m16, sum(t1.m17) as m17, sum(t1.m18) as m18, sum(t1.m19) as m19, sum(t1.m20) as m20, sum(t1.m21) as m21, sum(t1.m22) as m22, sum(t1.m23) as m23, sum(t1.m24) as m24, sum(t1.m25) as m25, sum(t1.m26) as m26, sum(t1.m27) as m27, sum(t1.m28) as m28, sum(t1.m29) as m29, sum(t1.m30) as m30, sum(t1.m31) as m31, sum(t1.m32) as m32, sum(t1.m33) as m33, sum(t1.m34) as m34, sum(t1.m35) as m35, sum(t1.m36) as m36, sum(t1.m37) as m37, sum(t1.m38) as m38, sum(t1.m39) as m39, sum(t1.m40) as m40, sum(t1.m41) as m41, sum(t1.m42) as m42, sum(t1.m43) as m43, sum(t1.m44) as m44, sum(t1.m45) as m45, sum(t1.m46) as m46, sum(t1.m47) as m47, sum(t1.m48) as m48, sum(t1.m49) as m49, sum(t1.m50) as m50, sum(t1.m51) as m51, sum(t1.m52) as m52, sum(t1.m53) as m53, sum(t1.m54) as m54, sum(t1.m55) as m55, sum(t1.m56) as m56, sum(t1.m57) as m57, sum(t1.m58) as m58, sum(t1.m59) as m59, sum(t1.m60) as m60 from fact_ra t1 where month >= '2008-4-1' and month <= '2013-11-1' and claim_owner like '%test%' and product_tree_1 = 'abc' group by financial_year </code></pre> <p>But the query takes a lot of time, here is the explain plan that I get:</p> <pre><code>+----+-------------+-------+------+------------------------------+--------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------------------+--------------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | t1 | ref | idx_product_tree_1,idx_month | idx_product_tree_1 | 137 | const | 1 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+------------------------------+--------------------+---------+-------+------+----------------------------------------------+ </code></pre> <p>How to turning performance about this query, thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload