New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Archiving performance: cache the slow segment subqueries that are using LIKE constraint #8850

Closed
quba opened this Issue Sep 24, 2015 · 11 comments

Comments

Projects
None yet
3 participants
@quba
Contributor

quba commented Sep 24, 2015

Hi,

if we are using segment with CONTAINS option using e.g. page URL, it's not possible to narrow down actions to a single ID. We have to execute a subquery that will get the list of IDs. In some cases it can be lots of IDs, even more than 100 000. Currently Piwik is executing a WHERE IN clause with a subquery querying log_action table. Such queries are really slow when using MySQL/MariaDB.
See:
https://www.percona.com/blog/2010/10/25/mysql-limitations-part-3-subqueries/
http://dba.stackexchange.com/questions/14565/mysql-subquery-slows-down-drastically-but-they-work-fine-independently/14581#14581

My proposal is to run such subqueries at the beginning of archiving process and cache them in a file. Therefore query for each report in Piwik will use the list of IDs instead of executing a subquery which may be really slow.

Here are some results for a subquery that returns more than 2000 action IDs:

Piwik query with segment subquery:

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted, 
    -> log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('%', '/some/page/url', '%')  AND type = 1 )) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (34.48 sec)

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted,  log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('%', '/some/page/url', '%')  AND type = 1 )) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (24.97 sec)

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted,  log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('%', '/some/page/url', '%')  AND type = 1 )) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (30.87 sec)

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted,  log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('%', '/some/page/url', '%')  AND type = 1 )) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (37.81 sec)

Piwik query with list of action IDs in WHERE IN():

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted, 
    -> log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (242939,177719,248769,783585,109010,154624,37560,391271,592547,166325,165083,364482,755173,523893,19568,38650,445751,94450,37804,17430,21106,141659,414751,269355,136395,129305,293931,50856,238649,199258,111114,9234,6076,28762,262481,19532,9230,238075,656879,6362,754045,252567,667149,82626,436837,89038,39522,79968,604277,34340,451653,209095,424193,8388,39010,272241,134116,147663,209845,886293,143591,136275,327561,239439,662497,39092,25716,253223,763385,7014,88516,268901,35516,238651,450893,867739,212339,80572,159026,351956,209619,187197,8042,572543,260841,136083,266923,391813,117846,19352,437579,206345,687847,668043,29244,418159,243695,470495,732337,762195,652239,616671,194916,249105,587193,19474,366358,753705,101080,6064,162568,4358,24510,736523,235225,28740,355714,289603,149651,178605,119907,375258,707351,187525,159746,141371,19652,415147,603015,853291,222453,408643,427003,124005,173005,139483,325509,220655,373648,116396,687971,22118,79984,240257,244545,265349,37734,20918,168517,434583,19334,315343,119883,827561,350112,9226,184429,84982,96692,8118,136499,605627,464207,327625,163052,618477,456253,19612,123977,6128,28532,136501,7804,166347,373702,456789,217907,251031,8046,604177,764269,253031,28886,406385,295967,224355,300340,401911,613207,57650,243715,389109,6980,242895,476159,462285,141033,356404,19608,39950,119869,6850,171169,488641,197310,199800,5676,260485,804795,115378,7894,225079,453609,640015,327113,272243,257289,304085,107060,303725,258223,618393,767781,163070,697287,300134,23518,216147,732771,360204,242169,547929,684605,7888,248731,57060,7802,342965,424683,617253,27776,610227,444089,444091,444093,444095,141657,749675,217475,160660,458721,483437,19610,618465,209375,19336,242069,160310,223841,251045,142705,23026,456813,245737,302089,364516,6050,324647,348556,184443,860263,805495,544277,21362,217895,8390,222025,405551,538001,439439,208015,162600,126477,264371,456491,456493,35848,115150,754255,136441,231241,768329,540343,341407,184955,330659,172481,7132,783191,35426,139545,21088,8038,249071,291703,399641,242541,259571,155812,700895,147857,91924,808507,216289,453871,238069,19512,8380,833051,160322,475383,91942,319483,7890,266999,19282,159886,125511,21192,189227,444677,592157,162162,543559,425041,6070,20974,226025,247449,25422,581327,209705,610219,136103,8558,818675,302811,756593,296569,134216,882753,372270,224287,110376,92424,188055,846721,155562,180973,871471,131786,768217,19624,28608,7018,21446,149589,230755,664703,49072,338551,116726,67826,484769,198972,128215,155820,8734,125207,425805,39670,179029,143133,613293,35600,19116,199306,119383,98130,38310,655343,59534,39674,116394,638981,248417,540779,185999,744875,199816,361364,396951,195764,674073,244709,151810,296113,474005,129703,19054,744937,6724,302307,137133,295417,77486,126075,317335,92990,8770,19346,631063,226883,252999,172891,803771,514887,233737,476209,289703,343293,21408,95046,721697,268525,17442,232909,269083,897151,837353,6144,96700,263373,344801,27932,541327,19576,320295,781131,173997,94518,197196,765727,257051,144081,202029,491733,783661,191535,99214,8562,233793,874921,684873,8608,91932,462261,9218,143595,476137,40056,140797,5274,25510,98576,129691,419325,477087,229591,149337,348574,483175,8044,254431,293939,169129,9014,785595,233755,649461,20926,609287,128067,790773,5526,96706,113162,617693,215465,96616,244851,136457,265197,787309,302115,19536,27392,476199,537709,46610,169205,136447,266101,94644,84000,152740,394385,362044,599791,198974,811535,5152,29070,3630,25568,444141,519963,204663,19290,34700,22760,367040,715909,17454,451753,27386,140931,131241,199878,106212,388973,185327,210445,29900,209747,142505,217343,172775,342315,833043,218249,126087,251413,398533,726313,17462,525635,185915,222435,136629,27778,489329,612533,17464,895337,6354,588431,151528,148023,524999,125943,881219,117264,695145,180619,27772,37568,654141,459221,149105,25538,78506,613863,164525,751831,239423,235845,370232,6712,7454,213529,132628,128985,451953,485331,777177,48202,117764,856791,155786,243487,463517,19080,3762,56864,615221,462275,96638,354200,510953,593657,17466,405531,92448,633009,76994,3626,180739,768389,655431,537149,593641,28082,19308,197144,220169,397117,296401,17452,673753,846183,428091,40080,35422,5590,263033,222161,502799,186949,88412,228793,455137,570429,273543,9216,228289,612499,527119,583049,223069,154874,19554,399083,795915,24328,328839,328841,328843,266715,141307,141283,586853,7448,4366,226041,184661,27774,17438,240091,138157,20096,25588,361876,273579,22778,25600,394369,216153,453629,270741,19478,194692,27928,616659,139491,19090,592579,251003,172903,9224,455461,735603,570477,209693,25532,476759,4012,406255,789925,313553,8120,157608,115896,182099,239381,592585,201220,190489,618527,260589,173497,689797,91920,51008,197170,523911,28712,738079,29562,242947,198880,236481,6054,233721,129077,164983,39946,456441,347044,692607,137511,755215,6316,8052,251231,163379,779069,416543,137443,789717,613655,6336,273527,213651,100236,170191,212381,38582,244875,264545,618535,103462,391521,334983,531933,132742,91420,318773,8778,425995,22714,472257,254013,412293,207043,588919,218849,840489,214583,5726,190771,19396,150158,613681,75278,562671,27842,588155,171703,577663,226295,311321,388067,233539,21440,325369,798391,224459,19342,125515,48798,396123,480625,146761,125959,146269,199598,197200,7892,291707,216629,149083,689783,783697,421659,609233,92410,755303,185561,694135,618035,290415,721873,367564,79792,8124,108534,577621,739129,462515,455111,248813,347036,356034,842437,24514,8114,110852,252303,291221,589885,141303,609011,170203,718623,790113,94918,92470,84004,456015,371278,289627,162584,263261,52386,220699,326509,29034,195006,19118,83314,408429,208069,371472,132332,779571,618617,244967,134764,162616,674063,411397,486819,304087,240771,266635,359192,577615,216167,217389,615905,198902,197708,808357,39902,421971,203563,184457,789457,39546,205181,146179,23602,534059,56924,160652,294767,229587,39800,5572,22064,210157,685247,7200,573645,269903,289715,544967,477893,19376,588459,227401,19360,173011,150578,209703,123975,188427,340331,858439,231169,617615,767273,232311,765745,773605,538175,170217,38780,67808,47180,68330,858393,49020,154896,458827,458833,458835,540235,136973,119923,186001,154828,137409,124515,355870,774027,8802,263713,236597,462269,247565,349722,456417,40180,302325,497041,19164,233713,342327,7302,334809,221203,9220,625375,272609,560169,25590,124967,21468,7444,19476,476153,864027,250719,499849,29242,344427,156608,376426,777871,251739,149765,615207,188401,243209,196070,893895,269273,20976,89034,362398,3602,117780,27978,150122,207065,8112,154890,94522,369662,39952,153626,149101,295991,376548,227181,8394,7024,220181,17456,247377,206985,431749,21262,707817,267045,166351,296007,28826,23450,617841,19418,20674,4014,208033,882769,224911,273569,297149,618241,186561,252037,785811,226673,65790,116486,7124,606523,181303,39732,720165,388703,21326,7128,711691,19572,49074,261891,128221,137299,252571,414623,216861,186319,3672,7012,273473,247725,25584,8084,194914,19578,492079,475361,206631,770471,124235,140957,401687,39794,808165,777689,122651,553209,385667,118666,157664,349756,213655,783691,7398,21452,130273,101982,46584,209823,154856,243577,78492,38580,96304,223283,128323,195258,475339,72400,728165,756559,574817,497279,136933,267565,46586,588391,242919,799755,496945,104222,220805,225109,438413,434815,296413,19314,107598,21470,233787,155258,7390,111030,53616,439215,242199,56216,356682,511047,863977,155510,3662,462115,401825,20672,149843,6074,845759,196894,34354,519059,297295,240175,145853,251975,695125,394265,684557,131245,8106,335727,180601,149421,36294,251621,195008,40204,39072,225991,29036,6356,293817,20908,427581,522179,615439,173409,419339,40220,7898,6052,220679,618513,735619,708645,8126,388391,188537,253993,91930,293901,724183,29352,618553,378440,687201,244441,716925,330675,858971,881259,326745,647479,149863,602923,250255,28812,154948,6718,436621,56218,116404,34352,7790,149091,186823,215873,510771,407351,6124,843599,241705,137131,205183,531911,360174,252043,79942,481479,215477,217629,29256,208175,8674,507391,19286,871455,300988,244073,6060,25580,57524,531923,154912,171701,390593,8108,194332,302079,225309,7150,6314,166425,5602,289615,718681,63974,143127,378452,5494,231177,8396,124629,181567,49032,330221,273583,39518,614649,577627,251155,215835,798373,250763,6328,6126,253987,172313,28810,7042,206023,399373,6382,614701,3690,184435,342287,4968,481377,19468,20098,110850,38524,126081,385149,805305,422841,187361,398077,427071,38214,149079,129303,567011,164863,233043,751889,150124,350432,226021,222447,668727,308847,269197,808333,833575,209103,112356,128199,136593,161710,244427,607727,6708,523761,6046,138683,7926,30896,92032,811371,39584,8040,251865,294699,214045,637293,38850,752439,778373,152124,136925,473285,37744,24912,616239,343767,8392,187131,729301,164541,47340,8102,248849,217651,664511,655209,21146,8056,577637,190191,3922,8072,783721,496161,78494,297211,239163,9222,125133,267811,50902,197938,23552,128325,86382,505233,245361,355660,22596,34348,137417,524277,104406,186791,112810,8838,23022,142711,267303,184297,20924,23024,248401,498635,9236,226915,186075,864079,864063,101820,322225,160124,433193,353676,128193,220573,404327,514923,542659,19292,46550,6720,107956,104444,370638,94366,164077,351680,817609,191601,497233,8100,272511,154290,504087,729887,35488,582353,753663,563903,456089,104234,39078,228923,157642,83840,24558,838575,811385,235579,163521,37572,534053,92440,166329,213535,260551,186623,46580,185961,708585,160034,136909,34336,507619,19464,6290,837347,178611,137369,261145,40212,432863,25414,649419,186187,243011,783685,342293,104246,150990,226515,153810,476067,21462,359180,25574,824663,398777,171709,607733,468813,667829,27926,476171,201514,172429,244813,104008,198910,21118,280741,261483,297465,592409,242931,245369,244637,726761,615061,84782,263363,618423,151898,634011,242089,226827,339737,39074,8800,450383,397995,131966,293915,19078,647445,272845,470763,319879,19520,6068,19170,593627,29082,703041,269353,857511,38618,199052,187921,7928,571601,103016,19304,184081,741659,6120,253043,577061,311071,189967,575811,594199,224135,651089,213479,267139,384577,687957,22994,7806,450625,272465,663719,409311,99826,38792,593055,497221,236885,19564,359200,260041,708569,640045,73704,201708,159996,155426,349430,8110,350558,212389,178669,512265,503767,588249,7038,872203,159672,137461,187277,467467,19120,717993,52362,132724,404315,31574,796775,372000,21364,19082,856769,149089,416285,21370,76996,99196,268165,104402,129967,250553,20858,577051,216423,216543,241413,316329,295013,325017,28530,403959,301428,125949,155564,260637,9238,101794,5628,73278,881101,235239,219015,143635,7780,25700,27868,223339,210441,407395,878389,6322,807367,732345,144465,35742,156132,517091,454195,656777,233791,135815,523707,366416,37738,182467,314585,844257,115726,103658,82628,162574,463171,664675,155566,137715,43726,28068,34704,196318,505505,604009,536163,28876,206831,125083,194426,209743,613127,7064,22718,125143,842283,592599,524769,151886,674047,405419,211665,7906,237961,292849,342895,209735,37786,675589,7900,171705,77952,243173,220607,40210,615169,337981,268861,424709,19556,248673,397475,409177,167355,8610,186007,451699,593673,250293,248207,185567,592417,19088,82652,7022,495449,296451,198958,121817,763011,132266,219573,410031,308791,197932,317425,813167,268467,162668,89030,328495,739667,320411,21432,201234,355706,454289,270165,244939,815819,684593,470087,249727,8122,864171,389755,35956,197934,240581,588159,6704,7168,517313,225975,113130,21450,19154,6366,140953,699317,876247,17496,19370,881283,129039,829151,209037,84590,810543,816613,215709,92434,738021,8840,250069,573235,226741,19570,39516,76990,171707,243837,149081,221173,19178,7916,729309,142299,91314,706003,188541,171149,735629,795939,23498,148027,135419,207725,7016,167221,395843,338613,91870,225977,478027,293535,346628,38698,218225,140937,684633,616045,441271,24528,290427,199820,222085,187513,258365,697045,248767,6118,263311,107326,154854,481263,27924,226823,528793,242941,7136,216323,146601,198882,388733,19288,208075,654127,236733,302847,8774,860175,455767,431767,233117,480701,132270,336411,301714,147859,780741,342503,23482,244725,267671,9232,531955,749595,115380,244513,452517,466287,187481,508265,564083,424145,93278,818657,840279,6364,19086,7394,242915,19604,684899,187523,149627,243201,6066,760657,19280,19052,157534,36834,134092,475015,744857,150492,136537,497437,7912,38666,506785,376554,448857,320445,768153,54212,216661,518963,224303,677669,19654,80764,564301,197302,186807,586125,631049,7386,39954,772141,248643,318877,491751,251803,25654,49822,5728,25708,199604,86278,585073,222923,456911,562725,143131,39050,304111,248665,19312,197632,7062,269073,809609,25408,131411,128557,160840,662543,252677,139045,695133,783301,140943,173995,520093,6368,153818,24560,182743,886705,489171,576707,687959,180159,19600,385165,289597,7924,184303,104256,244325,610233,655407,154650,36880,189235,342267,349866,182705,187515,354376,182429,21538,476189,140127,652219,588363,835831,39076,52364,863714,748587,19562,324073,223325,391499,5708,220903,38632,592449,96614,519631,796219,297103,593081,136107,37558,3648,92044,780803,360348,226067,273529,136573,324851,248863,19284,185533,181917,300654,289625,91766,128173,871427,101002,375938,153816,56214,199808,56892,464321,192345,201216,695219,609003,226555,151344,96608,198886,257209,846205,154876,213757,185969,330625,260453,468587,739283,57064,154006,171955,113160,140955,194292,195724,230461,233655,295229,512271,228295,249459,386447,19102,700915,150144,159814,243729,634043,39476,678601,162612,6062,814505,103664,259821,535361,703303,615475,802093,630343,243009,9228,273483,296005,19574,269357,467221,22600,17460,708593,40050,311021,191869,340349,8050,756573,448363,206915,431757,194910,264695,481399,125135,408151,143593,83834,141313,503149,547455,755263,234781,91934,142273,19566,294877,176227,410189,517689,462455,52384,164961,3898,818627,783637,194952,39892,124669,700613,8090,476051,154884,56900,290409,576721,30894,20902,750913,140799,520579,21448,180597,131978,523777,365426,886415,438513,149521,3664,617673,6320,819807,7040,683581,254045,413799,198966,206037,27780,266435,222643,7808,354412,244905,184507,128331,186275,124283,25430,40060,154908,215457,198140,389397,20906,177373,149347) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (2.72 sec)

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted,  log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (242939,177719,248769,783585,109010,154624,37560,391271,592547,166325,165083,364482,755173,523893,19568,38650,445751,94450,37804,17430,21106,141659,414751,269355,136395,129305,293931,50856,238649,199258,111114,9234,6076,28762,262481,19532,9230,238075,656879,6362,754045,252567,667149,82626,436837,89038,39522,79968,604277,34340,451653,209095,424193,8388,39010,272241,134116,147663,209845,886293,143591,136275,327561,239439,662497,39092,25716,253223,763385,7014,88516,268901,35516,238651,450893,867739,212339,80572,159026,351956,209619,187197,8042,572543,260841,136083,266923,391813,117846,19352,437579,206345,687847,668043,29244,418159,243695,470495,732337,762195,652239,616671,194916,249105,587193,19474,366358,753705,101080,6064,162568,4358,24510,736523,235225,28740,355714,289603,149651,178605,119907,375258,707351,187525,159746,141371,19652,415147,603015,853291,222453,408643,427003,124005,173005,139483,325509,220655,373648,116396,687971,22118,79984,240257,244545,265349,37734,20918,168517,434583,19334,315343,119883,827561,350112,9226,184429,84982,96692,8118,136499,605627,464207,327625,163052,618477,456253,19612,123977,6128,28532,136501,7804,166347,373702,456789,217907,251031,8046,604177,764269,253031,28886,406385,295967,224355,300340,401911,613207,57650,243715,389109,6980,242895,476159,462285,141033,356404,19608,39950,119869,6850,171169,488641,197310,199800,5676,260485,804795,115378,7894,225079,453609,640015,327113,272243,257289,304085,107060,303725,258223,618393,767781,163070,697287,300134,23518,216147,732771,360204,242169,547929,684605,7888,248731,57060,7802,342965,424683,617253,27776,610227,444089,444091,444093,444095,141657,749675,217475,160660,458721,483437,19610,618465,209375,19336,242069,160310,223841,251045,142705,23026,456813,245737,302089,364516,6050,324647,348556,184443,860263,805495,544277,21362,217895,8390,222025,405551,538001,439439,208015,162600,126477,264371,456491,456493,35848,115150,754255,136441,231241,768329,540343,341407,184955,330659,172481,7132,783191,35426,139545,21088,8038,249071,291703,399641,242541,259571,155812,700895,147857,91924,808507,216289,453871,238069,19512,8380,833051,160322,475383,91942,319483,7890,266999,19282,159886,125511,21192,189227,444677,592157,162162,543559,425041,6070,20974,226025,247449,25422,581327,209705,610219,136103,8558,818675,302811,756593,296569,134216,882753,372270,224287,110376,92424,188055,846721,155562,180973,871471,131786,768217,19624,28608,7018,21446,149589,230755,664703,49072,338551,116726,67826,484769,198972,128215,155820,8734,125207,425805,39670,179029,143133,613293,35600,19116,199306,119383,98130,38310,655343,59534,39674,116394,638981,248417,540779,185999,744875,199816,361364,396951,195764,674073,244709,151810,296113,474005,129703,19054,744937,6724,302307,137133,295417,77486,126075,317335,92990,8770,19346,631063,226883,252999,172891,803771,514887,233737,476209,289703,343293,21408,95046,721697,268525,17442,232909,269083,897151,837353,6144,96700,263373,344801,27932,541327,19576,320295,781131,173997,94518,197196,765727,257051,144081,202029,491733,783661,191535,99214,8562,233793,874921,684873,8608,91932,462261,9218,143595,476137,40056,140797,5274,25510,98576,129691,419325,477087,229591,149337,348574,483175,8044,254431,293939,169129,9014,785595,233755,649461,20926,609287,128067,790773,5526,96706,113162,617693,215465,96616,244851,136457,265197,787309,302115,19536,27392,476199,537709,46610,169205,136447,266101,94644,84000,152740,394385,362044,599791,198974,811535,5152,29070,3630,25568,444141,519963,204663,19290,34700,22760,367040,715909,17454,451753,27386,140931,131241,199878,106212,388973,185327,210445,29900,209747,142505,217343,172775,342315,833043,218249,126087,251413,398533,726313,17462,525635,185915,222435,136629,27778,489329,612533,17464,895337,6354,588431,151528,148023,524999,125943,881219,117264,695145,180619,27772,37568,654141,459221,149105,25538,78506,613863,164525,751831,239423,235845,370232,6712,7454,213529,132628,128985,451953,485331,777177,48202,117764,856791,155786,243487,463517,19080,3762,56864,615221,462275,96638,354200,510953,593657,17466,405531,92448,633009,76994,3626,180739,768389,655431,537149,593641,28082,19308,197144,220169,397117,296401,17452,673753,846183,428091,40080,35422,5590,263033,222161,502799,186949,88412,228793,455137,570429,273543,9216,228289,612499,527119,583049,223069,154874,19554,399083,795915,24328,328839,328841,328843,266715,141307,141283,586853,7448,4366,226041,184661,27774,17438,240091,138157,20096,25588,361876,273579,22778,25600,394369,216153,453629,270741,19478,194692,27928,616659,139491,19090,592579,251003,172903,9224,455461,735603,570477,209693,25532,476759,4012,406255,789925,313553,8120,157608,115896,182099,239381,592585,201220,190489,618527,260589,173497,689797,91920,51008,197170,523911,28712,738079,29562,242947,198880,236481,6054,233721,129077,164983,39946,456441,347044,692607,137511,755215,6316,8052,251231,163379,779069,416543,137443,789717,613655,6336,273527,213651,100236,170191,212381,38582,244875,264545,618535,103462,391521,334983,531933,132742,91420,318773,8778,425995,22714,472257,254013,412293,207043,588919,218849,840489,214583,5726,190771,19396,150158,613681,75278,562671,27842,588155,171703,577663,226295,311321,388067,233539,21440,325369,798391,224459,19342,125515,48798,396123,480625,146761,125959,146269,199598,197200,7892,291707,216629,149083,689783,783697,421659,609233,92410,755303,185561,694135,618035,290415,721873,367564,79792,8124,108534,577621,739129,462515,455111,248813,347036,356034,842437,24514,8114,110852,252303,291221,589885,141303,609011,170203,718623,790113,94918,92470,84004,456015,371278,289627,162584,263261,52386,220699,326509,29034,195006,19118,83314,408429,208069,371472,132332,779571,618617,244967,134764,162616,674063,411397,486819,304087,240771,266635,359192,577615,216167,217389,615905,198902,197708,808357,39902,421971,203563,184457,789457,39546,205181,146179,23602,534059,56924,160652,294767,229587,39800,5572,22064,210157,685247,7200,573645,269903,289715,544967,477893,19376,588459,227401,19360,173011,150578,209703,123975,188427,340331,858439,231169,617615,767273,232311,765745,773605,538175,170217,38780,67808,47180,68330,858393,49020,154896,458827,458833,458835,540235,136973,119923,186001,154828,137409,124515,355870,774027,8802,263713,236597,462269,247565,349722,456417,40180,302325,497041,19164,233713,342327,7302,334809,221203,9220,625375,272609,560169,25590,124967,21468,7444,19476,476153,864027,250719,499849,29242,344427,156608,376426,777871,251739,149765,615207,188401,243209,196070,893895,269273,20976,89034,362398,3602,117780,27978,150122,207065,8112,154890,94522,369662,39952,153626,149101,295991,376548,227181,8394,7024,220181,17456,247377,206985,431749,21262,707817,267045,166351,296007,28826,23450,617841,19418,20674,4014,208033,882769,224911,273569,297149,618241,186561,252037,785811,226673,65790,116486,7124,606523,181303,39732,720165,388703,21326,7128,711691,19572,49074,261891,128221,137299,252571,414623,216861,186319,3672,7012,273473,247725,25584,8084,194914,19578,492079,475361,206631,770471,124235,140957,401687,39794,808165,777689,122651,553209,385667,118666,157664,349756,213655,783691,7398,21452,130273,101982,46584,209823,154856,243577,78492,38580,96304,223283,128323,195258,475339,72400,728165,756559,574817,497279,136933,267565,46586,588391,242919,799755,496945,104222,220805,225109,438413,434815,296413,19314,107598,21470,233787,155258,7390,111030,53616,439215,242199,56216,356682,511047,863977,155510,3662,462115,401825,20672,149843,6074,845759,196894,34354,519059,297295,240175,145853,251975,695125,394265,684557,131245,8106,335727,180601,149421,36294,251621,195008,40204,39072,225991,29036,6356,293817,20908,427581,522179,615439,173409,419339,40220,7898,6052,220679,618513,735619,708645,8126,388391,188537,253993,91930,293901,724183,29352,618553,378440,687201,244441,716925,330675,858971,881259,326745,647479,149863,602923,250255,28812,154948,6718,436621,56218,116404,34352,7790,149091,186823,215873,510771,407351,6124,843599,241705,137131,205183,531911,360174,252043,79942,481479,215477,217629,29256,208175,8674,507391,19286,871455,300988,244073,6060,25580,57524,531923,154912,171701,390593,8108,194332,302079,225309,7150,6314,166425,5602,289615,718681,63974,143127,378452,5494,231177,8396,124629,181567,49032,330221,273583,39518,614649,577627,251155,215835,798373,250763,6328,6126,253987,172313,28810,7042,206023,399373,6382,614701,3690,184435,342287,4968,481377,19468,20098,110850,38524,126081,385149,805305,422841,187361,398077,427071,38214,149079,129303,567011,164863,233043,751889,150124,350432,226021,222447,668727,308847,269197,808333,833575,209103,112356,128199,136593,161710,244427,607727,6708,523761,6046,138683,7926,30896,92032,811371,39584,8040,251865,294699,214045,637293,38850,752439,778373,152124,136925,473285,37744,24912,616239,343767,8392,187131,729301,164541,47340,8102,248849,217651,664511,655209,21146,8056,577637,190191,3922,8072,783721,496161,78494,297211,239163,9222,125133,267811,50902,197938,23552,128325,86382,505233,245361,355660,22596,34348,137417,524277,104406,186791,112810,8838,23022,142711,267303,184297,20924,23024,248401,498635,9236,226915,186075,864079,864063,101820,322225,160124,433193,353676,128193,220573,404327,514923,542659,19292,46550,6720,107956,104444,370638,94366,164077,351680,817609,191601,497233,8100,272511,154290,504087,729887,35488,582353,753663,563903,456089,104234,39078,228923,157642,83840,24558,838575,811385,235579,163521,37572,534053,92440,166329,213535,260551,186623,46580,185961,708585,160034,136909,34336,507619,19464,6290,837347,178611,137369,261145,40212,432863,25414,649419,186187,243011,783685,342293,104246,150990,226515,153810,476067,21462,359180,25574,824663,398777,171709,607733,468813,667829,27926,476171,201514,172429,244813,104008,198910,21118,280741,261483,297465,592409,242931,245369,244637,726761,615061,84782,263363,618423,151898,634011,242089,226827,339737,39074,8800,450383,397995,131966,293915,19078,647445,272845,470763,319879,19520,6068,19170,593627,29082,703041,269353,857511,38618,199052,187921,7928,571601,103016,19304,184081,741659,6120,253043,577061,311071,189967,575811,594199,224135,651089,213479,267139,384577,687957,22994,7806,450625,272465,663719,409311,99826,38792,593055,497221,236885,19564,359200,260041,708569,640045,73704,201708,159996,155426,349430,8110,350558,212389,178669,512265,503767,588249,7038,872203,159672,137461,187277,467467,19120,717993,52362,132724,404315,31574,796775,372000,21364,19082,856769,149089,416285,21370,76996,99196,268165,104402,129967,250553,20858,577051,216423,216543,241413,316329,295013,325017,28530,403959,301428,125949,155564,260637,9238,101794,5628,73278,881101,235239,219015,143635,7780,25700,27868,223339,210441,407395,878389,6322,807367,732345,144465,35742,156132,517091,454195,656777,233791,135815,523707,366416,37738,182467,314585,844257,115726,103658,82628,162574,463171,664675,155566,137715,43726,28068,34704,196318,505505,604009,536163,28876,206831,125083,194426,209743,613127,7064,22718,125143,842283,592599,524769,151886,674047,405419,211665,7906,237961,292849,342895,209735,37786,675589,7900,171705,77952,243173,220607,40210,615169,337981,268861,424709,19556,248673,397475,409177,167355,8610,186007,451699,593673,250293,248207,185567,592417,19088,82652,7022,495449,296451,198958,121817,763011,132266,219573,410031,308791,197932,317425,813167,268467,162668,89030,328495,739667,320411,21432,201234,355706,454289,270165,244939,815819,684593,470087,249727,8122,864171,389755,35956,197934,240581,588159,6704,7168,517313,225975,113130,21450,19154,6366,140953,699317,876247,17496,19370,881283,129039,829151,209037,84590,810543,816613,215709,92434,738021,8840,250069,573235,226741,19570,39516,76990,171707,243837,149081,221173,19178,7916,729309,142299,91314,706003,188541,171149,735629,795939,23498,148027,135419,207725,7016,167221,395843,338613,91870,225977,478027,293535,346628,38698,218225,140937,684633,616045,441271,24528,290427,199820,222085,187513,258365,697045,248767,6118,263311,107326,154854,481263,27924,226823,528793,242941,7136,216323,146601,198882,388733,19288,208075,654127,236733,302847,8774,860175,455767,431767,233117,480701,132270,336411,301714,147859,780741,342503,23482,244725,267671,9232,531955,749595,115380,244513,452517,466287,187481,508265,564083,424145,93278,818657,840279,6364,19086,7394,242915,19604,684899,187523,149627,243201,6066,760657,19280,19052,157534,36834,134092,475015,744857,150492,136537,497437,7912,38666,506785,376554,448857,320445,768153,54212,216661,518963,224303,677669,19654,80764,564301,197302,186807,586125,631049,7386,39954,772141,248643,318877,491751,251803,25654,49822,5728,25708,199604,86278,585073,222923,456911,562725,143131,39050,304111,248665,19312,197632,7062,269073,809609,25408,131411,128557,160840,662543,252677,139045,695133,783301,140943,173995,520093,6368,153818,24560,182743,886705,489171,576707,687959,180159,19600,385165,289597,7924,184303,104256,244325,610233,655407,154650,36880,189235,342267,349866,182705,187515,354376,182429,21538,476189,140127,652219,588363,835831,39076,52364,863714,748587,19562,324073,223325,391499,5708,220903,38632,592449,96614,519631,796219,297103,593081,136107,37558,3648,92044,780803,360348,226067,273529,136573,324851,248863,19284,185533,181917,300654,289625,91766,128173,871427,101002,375938,153816,56214,199808,56892,464321,192345,201216,695219,609003,226555,151344,96608,198886,257209,846205,154876,213757,185969,330625,260453,468587,739283,57064,154006,171955,113160,140955,194292,195724,230461,233655,295229,512271,228295,249459,386447,19102,700915,150144,159814,243729,634043,39476,678601,162612,6062,814505,103664,259821,535361,703303,615475,802093,630343,243009,9228,273483,296005,19574,269357,467221,22600,17460,708593,40050,311021,191869,340349,8050,756573,448363,206915,431757,194910,264695,481399,125135,408151,143593,83834,141313,503149,547455,755263,234781,91934,142273,19566,294877,176227,410189,517689,462455,52384,164961,3898,818627,783637,194952,39892,124669,700613,8090,476051,154884,56900,290409,576721,30894,20902,750913,140799,520579,21448,180597,131978,523777,365426,886415,438513,149521,3664,617673,6320,819807,7040,683581,254045,413799,198966,206037,27780,266435,222643,7808,354412,244905,184507,128331,186275,124283,25430,40060,154908,215457,198140,389397,20906,177373,149347) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (2.74 sec)

mysql> SELECT log_inner.config_resolution AS `label`, count(distinct log_inner.idvisitor) AS `1`, count(*) AS `2`, sum(log_inner.visit_total_actions) AS `3`, max(log_inner.visit_total_actions) AS `4`, sum(log_inner.visit_total_time) AS `5`, sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, count(distinct log_inner.user_id) AS `39` FROM (SELECT log_visit.config_resolution, log_visit.idvisitor, log_visit.visit_total_actions, log_visit.visit_total_time, log_visit.visit_goal_converted,  log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2015-09-23 22:00:00' AND log_visit.visit_last_action_time <= '2015-09-24 21:59:59' AND log_visit.idsite IN ('6') ) AND ( ( log_link_visit_action.idaction_url IN (242939,177719,248769,783585,109010,154624,37560,391271,592547,166325,165083,364482,755173,523893,19568,38650,445751,94450,37804,17430,21106,141659,414751,269355,136395,129305,293931,50856,238649,199258,111114,9234,6076,28762,262481,19532,9230,238075,656879,6362,754045,252567,667149,82626,436837,89038,39522,79968,604277,34340,451653,209095,424193,8388,39010,272241,134116,147663,209845,886293,143591,136275,327561,239439,662497,39092,25716,253223,763385,7014,88516,268901,35516,238651,450893,867739,212339,80572,159026,351956,209619,187197,8042,572543,260841,136083,266923,391813,117846,19352,437579,206345,687847,668043,29244,418159,243695,470495,732337,762195,652239,616671,194916,249105,587193,19474,366358,753705,101080,6064,162568,4358,24510,736523,235225,28740,355714,289603,149651,178605,119907,375258,707351,187525,159746,141371,19652,415147,603015,853291,222453,408643,427003,124005,173005,139483,325509,220655,373648,116396,687971,22118,79984,240257,244545,265349,37734,20918,168517,434583,19334,315343,119883,827561,350112,9226,184429,84982,96692,8118,136499,605627,464207,327625,163052,618477,456253,19612,123977,6128,28532,136501,7804,166347,373702,456789,217907,251031,8046,604177,764269,253031,28886,406385,295967,224355,300340,401911,613207,57650,243715,389109,6980,242895,476159,462285,141033,356404,19608,39950,119869,6850,171169,488641,197310,199800,5676,260485,804795,115378,7894,225079,453609,640015,327113,272243,257289,304085,107060,303725,258223,618393,767781,163070,697287,300134,23518,216147,732771,360204,242169,547929,684605,7888,248731,57060,7802,342965,424683,617253,27776,610227,444089,444091,444093,444095,141657,749675,217475,160660,458721,483437,19610,618465,209375,19336,242069,160310,223841,251045,142705,23026,456813,245737,302089,364516,6050,324647,348556,184443,860263,805495,544277,21362,217895,8390,222025,405551,538001,439439,208015,162600,126477,264371,456491,456493,35848,115150,754255,136441,231241,768329,540343,341407,184955,330659,172481,7132,783191,35426,139545,21088,8038,249071,291703,399641,242541,259571,155812,700895,147857,91924,808507,216289,453871,238069,19512,8380,833051,160322,475383,91942,319483,7890,266999,19282,159886,125511,21192,189227,444677,592157,162162,543559,425041,6070,20974,226025,247449,25422,581327,209705,610219,136103,8558,818675,302811,756593,296569,134216,882753,372270,224287,110376,92424,188055,846721,155562,180973,871471,131786,768217,19624,28608,7018,21446,149589,230755,664703,49072,338551,116726,67826,484769,198972,128215,155820,8734,125207,425805,39670,179029,143133,613293,35600,19116,199306,119383,98130,38310,655343,59534,39674,116394,638981,248417,540779,185999,744875,199816,361364,396951,195764,674073,244709,151810,296113,474005,129703,19054,744937,6724,302307,137133,295417,77486,126075,317335,92990,8770,19346,631063,226883,252999,172891,803771,514887,233737,476209,289703,343293,21408,95046,721697,268525,17442,232909,269083,897151,837353,6144,96700,263373,344801,27932,541327,19576,320295,781131,173997,94518,197196,765727,257051,144081,202029,491733,783661,191535,99214,8562,233793,874921,684873,8608,91932,462261,9218,143595,476137,40056,140797,5274,25510,98576,129691,419325,477087,229591,149337,348574,483175,8044,254431,293939,169129,9014,785595,233755,649461,20926,609287,128067,790773,5526,96706,113162,617693,215465,96616,244851,136457,265197,787309,302115,19536,27392,476199,537709,46610,169205,136447,266101,94644,84000,152740,394385,362044,599791,198974,811535,5152,29070,3630,25568,444141,519963,204663,19290,34700,22760,367040,715909,17454,451753,27386,140931,131241,199878,106212,388973,185327,210445,29900,209747,142505,217343,172775,342315,833043,218249,126087,251413,398533,726313,17462,525635,185915,222435,136629,27778,489329,612533,17464,895337,6354,588431,151528,148023,524999,125943,881219,117264,695145,180619,27772,37568,654141,459221,149105,25538,78506,613863,164525,751831,239423,235845,370232,6712,7454,213529,132628,128985,451953,485331,777177,48202,117764,856791,155786,243487,463517,19080,3762,56864,615221,462275,96638,354200,510953,593657,17466,405531,92448,633009,76994,3626,180739,768389,655431,537149,593641,28082,19308,197144,220169,397117,296401,17452,673753,846183,428091,40080,35422,5590,263033,222161,502799,186949,88412,228793,455137,570429,273543,9216,228289,612499,527119,583049,223069,154874,19554,399083,795915,24328,328839,328841,328843,266715,141307,141283,586853,7448,4366,226041,184661,27774,17438,240091,138157,20096,25588,361876,273579,22778,25600,394369,216153,453629,270741,19478,194692,27928,616659,139491,19090,592579,251003,172903,9224,455461,735603,570477,209693,25532,476759,4012,406255,789925,313553,8120,157608,115896,182099,239381,592585,201220,190489,618527,260589,173497,689797,91920,51008,197170,523911,28712,738079,29562,242947,198880,236481,6054,233721,129077,164983,39946,456441,347044,692607,137511,755215,6316,8052,251231,163379,779069,416543,137443,789717,613655,6336,273527,213651,100236,170191,212381,38582,244875,264545,618535,103462,391521,334983,531933,132742,91420,318773,8778,425995,22714,472257,254013,412293,207043,588919,218849,840489,214583,5726,190771,19396,150158,613681,75278,562671,27842,588155,171703,577663,226295,311321,388067,233539,21440,325369,798391,224459,19342,125515,48798,396123,480625,146761,125959,146269,199598,197200,7892,291707,216629,149083,689783,783697,421659,609233,92410,755303,185561,694135,618035,290415,721873,367564,79792,8124,108534,577621,739129,462515,455111,248813,347036,356034,842437,24514,8114,110852,252303,291221,589885,141303,609011,170203,718623,790113,94918,92470,84004,456015,371278,289627,162584,263261,52386,220699,326509,29034,195006,19118,83314,408429,208069,371472,132332,779571,618617,244967,134764,162616,674063,411397,486819,304087,240771,266635,359192,577615,216167,217389,615905,198902,197708,808357,39902,421971,203563,184457,789457,39546,205181,146179,23602,534059,56924,160652,294767,229587,39800,5572,22064,210157,685247,7200,573645,269903,289715,544967,477893,19376,588459,227401,19360,173011,150578,209703,123975,188427,340331,858439,231169,617615,767273,232311,765745,773605,538175,170217,38780,67808,47180,68330,858393,49020,154896,458827,458833,458835,540235,136973,119923,186001,154828,137409,124515,355870,774027,8802,263713,236597,462269,247565,349722,456417,40180,302325,497041,19164,233713,342327,7302,334809,221203,9220,625375,272609,560169,25590,124967,21468,7444,19476,476153,864027,250719,499849,29242,344427,156608,376426,777871,251739,149765,615207,188401,243209,196070,893895,269273,20976,89034,362398,3602,117780,27978,150122,207065,8112,154890,94522,369662,39952,153626,149101,295991,376548,227181,8394,7024,220181,17456,247377,206985,431749,21262,707817,267045,166351,296007,28826,23450,617841,19418,20674,4014,208033,882769,224911,273569,297149,618241,186561,252037,785811,226673,65790,116486,7124,606523,181303,39732,720165,388703,21326,7128,711691,19572,49074,261891,128221,137299,252571,414623,216861,186319,3672,7012,273473,247725,25584,8084,194914,19578,492079,475361,206631,770471,124235,140957,401687,39794,808165,777689,122651,553209,385667,118666,157664,349756,213655,783691,7398,21452,130273,101982,46584,209823,154856,243577,78492,38580,96304,223283,128323,195258,475339,72400,728165,756559,574817,497279,136933,267565,46586,588391,242919,799755,496945,104222,220805,225109,438413,434815,296413,19314,107598,21470,233787,155258,7390,111030,53616,439215,242199,56216,356682,511047,863977,155510,3662,462115,401825,20672,149843,6074,845759,196894,34354,519059,297295,240175,145853,251975,695125,394265,684557,131245,8106,335727,180601,149421,36294,251621,195008,40204,39072,225991,29036,6356,293817,20908,427581,522179,615439,173409,419339,40220,7898,6052,220679,618513,735619,708645,8126,388391,188537,253993,91930,293901,724183,29352,618553,378440,687201,244441,716925,330675,858971,881259,326745,647479,149863,602923,250255,28812,154948,6718,436621,56218,116404,34352,7790,149091,186823,215873,510771,407351,6124,843599,241705,137131,205183,531911,360174,252043,79942,481479,215477,217629,29256,208175,8674,507391,19286,871455,300988,244073,6060,25580,57524,531923,154912,171701,390593,8108,194332,302079,225309,7150,6314,166425,5602,289615,718681,63974,143127,378452,5494,231177,8396,124629,181567,49032,330221,273583,39518,614649,577627,251155,215835,798373,250763,6328,6126,253987,172313,28810,7042,206023,399373,6382,614701,3690,184435,342287,4968,481377,19468,20098,110850,38524,126081,385149,805305,422841,187361,398077,427071,38214,149079,129303,567011,164863,233043,751889,150124,350432,226021,222447,668727,308847,269197,808333,833575,209103,112356,128199,136593,161710,244427,607727,6708,523761,6046,138683,7926,30896,92032,811371,39584,8040,251865,294699,214045,637293,38850,752439,778373,152124,136925,473285,37744,24912,616239,343767,8392,187131,729301,164541,47340,8102,248849,217651,664511,655209,21146,8056,577637,190191,3922,8072,783721,496161,78494,297211,239163,9222,125133,267811,50902,197938,23552,128325,86382,505233,245361,355660,22596,34348,137417,524277,104406,186791,112810,8838,23022,142711,267303,184297,20924,23024,248401,498635,9236,226915,186075,864079,864063,101820,322225,160124,433193,353676,128193,220573,404327,514923,542659,19292,46550,6720,107956,104444,370638,94366,164077,351680,817609,191601,497233,8100,272511,154290,504087,729887,35488,582353,753663,563903,456089,104234,39078,228923,157642,83840,24558,838575,811385,235579,163521,37572,534053,92440,166329,213535,260551,186623,46580,185961,708585,160034,136909,34336,507619,19464,6290,837347,178611,137369,261145,40212,432863,25414,649419,186187,243011,783685,342293,104246,150990,226515,153810,476067,21462,359180,25574,824663,398777,171709,607733,468813,667829,27926,476171,201514,172429,244813,104008,198910,21118,280741,261483,297465,592409,242931,245369,244637,726761,615061,84782,263363,618423,151898,634011,242089,226827,339737,39074,8800,450383,397995,131966,293915,19078,647445,272845,470763,319879,19520,6068,19170,593627,29082,703041,269353,857511,38618,199052,187921,7928,571601,103016,19304,184081,741659,6120,253043,577061,311071,189967,575811,594199,224135,651089,213479,267139,384577,687957,22994,7806,450625,272465,663719,409311,99826,38792,593055,497221,236885,19564,359200,260041,708569,640045,73704,201708,159996,155426,349430,8110,350558,212389,178669,512265,503767,588249,7038,872203,159672,137461,187277,467467,19120,717993,52362,132724,404315,31574,796775,372000,21364,19082,856769,149089,416285,21370,76996,99196,268165,104402,129967,250553,20858,577051,216423,216543,241413,316329,295013,325017,28530,403959,301428,125949,155564,260637,9238,101794,5628,73278,881101,235239,219015,143635,7780,25700,27868,223339,210441,407395,878389,6322,807367,732345,144465,35742,156132,517091,454195,656777,233791,135815,523707,366416,37738,182467,314585,844257,115726,103658,82628,162574,463171,664675,155566,137715,43726,28068,34704,196318,505505,604009,536163,28876,206831,125083,194426,209743,613127,7064,22718,125143,842283,592599,524769,151886,674047,405419,211665,7906,237961,292849,342895,209735,37786,675589,7900,171705,77952,243173,220607,40210,615169,337981,268861,424709,19556,248673,397475,409177,167355,8610,186007,451699,593673,250293,248207,185567,592417,19088,82652,7022,495449,296451,198958,121817,763011,132266,219573,410031,308791,197932,317425,813167,268467,162668,89030,328495,739667,320411,21432,201234,355706,454289,270165,244939,815819,684593,470087,249727,8122,864171,389755,35956,197934,240581,588159,6704,7168,517313,225975,113130,21450,19154,6366,140953,699317,876247,17496,19370,881283,129039,829151,209037,84590,810543,816613,215709,92434,738021,8840,250069,573235,226741,19570,39516,76990,171707,243837,149081,221173,19178,7916,729309,142299,91314,706003,188541,171149,735629,795939,23498,148027,135419,207725,7016,167221,395843,338613,91870,225977,478027,293535,346628,38698,218225,140937,684633,616045,441271,24528,290427,199820,222085,187513,258365,697045,248767,6118,263311,107326,154854,481263,27924,226823,528793,242941,7136,216323,146601,198882,388733,19288,208075,654127,236733,302847,8774,860175,455767,431767,233117,480701,132270,336411,301714,147859,780741,342503,23482,244725,267671,9232,531955,749595,115380,244513,452517,466287,187481,508265,564083,424145,93278,818657,840279,6364,19086,7394,242915,19604,684899,187523,149627,243201,6066,760657,19280,19052,157534,36834,134092,475015,744857,150492,136537,497437,7912,38666,506785,376554,448857,320445,768153,54212,216661,518963,224303,677669,19654,80764,564301,197302,186807,586125,631049,7386,39954,772141,248643,318877,491751,251803,25654,49822,5728,25708,199604,86278,585073,222923,456911,562725,143131,39050,304111,248665,19312,197632,7062,269073,809609,25408,131411,128557,160840,662543,252677,139045,695133,783301,140943,173995,520093,6368,153818,24560,182743,886705,489171,576707,687959,180159,19600,385165,289597,7924,184303,104256,244325,610233,655407,154650,36880,189235,342267,349866,182705,187515,354376,182429,21538,476189,140127,652219,588363,835831,39076,52364,863714,748587,19562,324073,223325,391499,5708,220903,38632,592449,96614,519631,796219,297103,593081,136107,37558,3648,92044,780803,360348,226067,273529,136573,324851,248863,19284,185533,181917,300654,289625,91766,128173,871427,101002,375938,153816,56214,199808,56892,464321,192345,201216,695219,609003,226555,151344,96608,198886,257209,846205,154876,213757,185969,330625,260453,468587,739283,57064,154006,171955,113160,140955,194292,195724,230461,233655,295229,512271,228295,249459,386447,19102,700915,150144,159814,243729,634043,39476,678601,162612,6062,814505,103664,259821,535361,703303,615475,802093,630343,243009,9228,273483,296005,19574,269357,467221,22600,17460,708593,40050,311021,191869,340349,8050,756573,448363,206915,431757,194910,264695,481399,125135,408151,143593,83834,141313,503149,547455,755263,234781,91934,142273,19566,294877,176227,410189,517689,462455,52384,164961,3898,818627,783637,194952,39892,124669,700613,8090,476051,154884,56900,290409,576721,30894,20902,750913,140799,520579,21448,180597,131978,523777,365426,886415,438513,149521,3664,617673,6320,819807,7040,683581,254045,413799,198966,206037,27780,266435,222643,7808,354412,244905,184507,128331,186275,124283,25430,40060,154908,215457,198140,389397,20906,177373,149347) ) ) GROUP BY log_visit.idvisit ORDER BY NULL) AS log_inner GROUP BY log_inner.config_resolution;
+-----------+---+---+------+------+------+------+------+----+
| label     | 1 | 2 | 3    | 4    | 5    | 6    | 7    | 39 |
+-----------+---+---+------+------+------+------+------+----+
| 1280x1024 | 1 | 1 |   48 |   48 |  525 |    0 |    1 |  0 |
| 1440x900  | 1 | 1 |   28 |   28 |  125 |    0 |    1 |  0 |
+-----------+---+---+------+------+------+------+------+----+
2 rows in set (2.73 sec)

FYI: I replaced real URL with an example.

Some calculations. In the best case we save here 21 seconds. Assuming that in Piwik we have 100 reports, it would speed up the archiving process by more than 30mins.

Some additional info:

$ mysql --version
mysql  Ver 15.1 Distrib 10.0.19-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Subquery cache is enabled.

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 24, 2015

Member

Not easily do-able. and not sure that we want to try this... It's complex: a segment like pageUrl@=o could match millions of rows. There could be several such segments in one segment string so we would end up having to print millions times N integer in the query: not a good idea. In general, this should be handled by Mysql, it looks like a bug that Mysql is not efficient with subqueries. Subquery cache is enabled, but is it used for this query?

Member

mattab commented Sep 24, 2015

Not easily do-able. and not sure that we want to try this... It's complex: a segment like pageUrl@=o could match millions of rows. There could be several such segments in one segment string so we would end up having to print millions times N integer in the query: not a good idea. In general, this should be handled by Mysql, it looks like a bug that Mysql is not efficient with subqueries. Subquery cache is enabled, but is it used for this query?

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 25, 2015

Member

A thought: one of the issue is that the subquery is slow because full text searching in the large table can be slow. To improve the speed of full text searching (ie. Contains, Does not contain) idea suggested by @diosmosis - maybe we could use a FULL TEXT index on the log_action.name column? It looks available in latest MariaDB (It used to be that FULL TEXT was only for Myisam tables but Piwik uses Innodb by default). reading MariaDB doc Fulltext index overview:

Full-text indexes can be used only with MyISAM and Aria tables, from MariaDB 10.0.5 with InnoDB tables and from MariaDB 10.0.15 with Mroonga tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

(we should use Boolean full-text searches to go above 50% threshold, and tweak appropriately innodb_ft_cache_size and innodb_ft_total_cache_size to size the fulltext index enough for the log_action table)

Member

mattab commented Sep 25, 2015

A thought: one of the issue is that the subquery is slow because full text searching in the large table can be slow. To improve the speed of full text searching (ie. Contains, Does not contain) idea suggested by @diosmosis - maybe we could use a FULL TEXT index on the log_action.name column? It looks available in latest MariaDB (It used to be that FULL TEXT was only for Myisam tables but Piwik uses Innodb by default). reading MariaDB doc Fulltext index overview:

Full-text indexes can be used only with MyISAM and Aria tables, from MariaDB 10.0.5 with InnoDB tables and from MariaDB 10.0.15 with Mroonga tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

(we should use Boolean full-text searches to go above 50% threshold, and tweak appropriately innodb_ft_cache_size and innodb_ft_total_cache_size to size the fulltext index enough for the log_action table)

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 28, 2015

Member

First thing to check when experiencing this issue is:

Fact:

  • Our subquery may not always be semi-JOINs because the segments may include OR statements where each statement includes a subquery. Rewriting a subquery into join is only possible when conditions are AND... refs https://mariadb.com/kb/en/mariadb/subquery-optimizations/
  • in general it should be up to mysql to do this caching and not us. I have a feeling that when Mysql does not use subquery caching it may be due to misconfiguration of DB server or bug in that mysql version.

In general, I didn't jump into implementing this solution because it has challenges

  • be careful as the subquery can often returns millions of IDs
  • therefore we'd need to print the IDs in the query only when < 1000 IDs or so... (we'd need to cache the fact that this query cannot be cached...)
  • we probably need to check max_allowed_packet and make sure the query size does not go over (or this would otherwise completely break Piwik and result in "no data reports")
  • we'd need to add a config setting and have it disabled by default (so we can test it internally on customers and find any bugs/regressions this may introduce)

One use case where caching IDs won't work

Imagine you create a edge case segment "Page URL contains 'i'" on demo.piwik.org (a medium sized piwik)

Creating a temp table with idaction has 1,255,519 results (took ~ 3 seconds).


CREATE TEMPORARY TABLE t1(
SELECT idaction
FROM piwik_log_action
WHERE (
name LIKE CONCAT( '%', 'i', '%' )
AND TYPE =1
)
);

Running the query re-using the temporary table (took 35 seconds:)

SELECT count(distinct log_inner.idvisitor) AS `1`, 
 count(*) AS `2`, 
 sum(log_inner.visit_total_actions) AS `3`, 
 max(log_inner.visit_total_actions) AS `4`, 
 sum(log_inner.visit_total_time) AS `5`, 
 sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, 
 sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, 
 count(distinct log_inner.user_id) AS `39` FROM (
 SELECT log_visit.idvisitor, 
log_visit.visit_total_actions, 
log_visit.visit_total_time, 
log_visit.visit_goal_converted, 
log_visit.user_id FROM
 piwik_log_visit AS log_visit
 LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
 WHERE
 ( log_visit.visit_last_action_time >= '2015-09-20 22:00:00'
 AND log_visit.visit_last_action_time <= '2015-09-21 21:59:59'
 AND log_visit.idsite IN ('3') )
 AND
 ( log_visit.visitor_returning = '0' AND ( log_link_visit_action.idaction_url IN ( SELECT * from t1) ) )
 GROUP BY
 log_visit.idvisit
 ORDER BY
 NULL
 ) AS log_inner;

---> in this case, because there are 1.2 M ids, printing IDs in query would not work and exceed max_allowed_packet

Member

mattab commented Sep 28, 2015

First thing to check when experiencing this issue is:

Fact:

  • Our subquery may not always be semi-JOINs because the segments may include OR statements where each statement includes a subquery. Rewriting a subquery into join is only possible when conditions are AND... refs https://mariadb.com/kb/en/mariadb/subquery-optimizations/
  • in general it should be up to mysql to do this caching and not us. I have a feeling that when Mysql does not use subquery caching it may be due to misconfiguration of DB server or bug in that mysql version.

In general, I didn't jump into implementing this solution because it has challenges

  • be careful as the subquery can often returns millions of IDs
  • therefore we'd need to print the IDs in the query only when < 1000 IDs or so... (we'd need to cache the fact that this query cannot be cached...)
  • we probably need to check max_allowed_packet and make sure the query size does not go over (or this would otherwise completely break Piwik and result in "no data reports")
  • we'd need to add a config setting and have it disabled by default (so we can test it internally on customers and find any bugs/regressions this may introduce)

One use case where caching IDs won't work

Imagine you create a edge case segment "Page URL contains 'i'" on demo.piwik.org (a medium sized piwik)

Creating a temp table with idaction has 1,255,519 results (took ~ 3 seconds).


CREATE TEMPORARY TABLE t1(
SELECT idaction
FROM piwik_log_action
WHERE (
name LIKE CONCAT( '%', 'i', '%' )
AND TYPE =1
)
);

Running the query re-using the temporary table (took 35 seconds:)

SELECT count(distinct log_inner.idvisitor) AS `1`, 
 count(*) AS `2`, 
 sum(log_inner.visit_total_actions) AS `3`, 
 max(log_inner.visit_total_actions) AS `4`, 
 sum(log_inner.visit_total_time) AS `5`, 
 sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, 
 sum(case log_inner.visit_goal_converted when 1 then 1 else 0 end) AS `7`, 
 count(distinct log_inner.user_id) AS `39` FROM (
 SELECT log_visit.idvisitor, 
log_visit.visit_total_actions, 
log_visit.visit_total_time, 
log_visit.visit_goal_converted, 
log_visit.user_id FROM
 piwik_log_visit AS log_visit
 LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
 WHERE
 ( log_visit.visit_last_action_time >= '2015-09-20 22:00:00'
 AND log_visit.visit_last_action_time <= '2015-09-21 21:59:59'
 AND log_visit.idsite IN ('3') )
 AND
 ( log_visit.visitor_returning = '0' AND ( log_link_visit_action.idaction_url IN ( SELECT * from t1) ) )
 GROUP BY
 log_visit.idvisit
 ORDER BY
 NULL
 ) AS log_inner;

---> in this case, because there are 1.2 M ids, printing IDs in query would not work and exceed max_allowed_packet

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 28, 2015

Member

it's do-able but needs some consideration. Will take a look at the code and see if it can be implemented quickly

Member

mattab commented Sep 28, 2015

it's do-able but needs some consideration. Will take a look at the code and see if it can be implemented quickly

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 28, 2015

Member

PR work in progress: #8861

Member

mattab commented Sep 28, 2015

PR work in progress: #8861

@mattab mattab added this to the 2.15.0 milestone Sep 29, 2015

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Sep 29, 2015

Member

Follow up issue: Enable segment subquery cache (queries with Contains/Does not contain on log_action) #8867

Before closing this issue, must follow up and improve as per @diosmosis review: #8861 (diff)

Member

mattab commented Sep 29, 2015

Follow up issue: Enable segment subquery cache (queries with Contains/Does not contain on log_action) #8867

Before closing this issue, must follow up and improve as per @diosmosis review: #8861 (diff)

@quba

This comment has been minimized.

Show comment
Hide comment
@quba

quba Sep 30, 2015

Contributor

@mattab have you tested it with browser archiving? I received feedback that it doesn't help in that case. Is it possible that each concurrent report request is trying to run segment subquery to warmup the cache? Currently it helps for sure with CLI archiving but we need to check browser archiving as well.

Contributor

quba commented Sep 30, 2015

@mattab have you tested it with browser archiving? I received feedback that it doesn't help in that case. Is it possible that each concurrent report request is trying to run segment subquery to warmup the cache? Currently it helps for sure with CLI archiving but we need to check browser archiving as well.

@quba

This comment has been minimized.

Show comment
Hide comment
@quba

quba Sep 30, 2015

Contributor

I tested it locally and segment query was executed only once. Would be nice to test how it works if this query is slow (e.g. takes longer than 2 sec).

Contributor

quba commented Sep 30, 2015

I tested it locally and segment query was executed only once. Would be nice to test how it works if this query is slow (e.g. takes longer than 2 sec).

@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Oct 1, 2015

Member

@quba it should work. if there is a problem, please get someone to write a good bug report here / re-open ticket

Member

mattab commented Oct 1, 2015

@quba it should work. if there is a problem, please get someone to write a good bug report here / re-open ticket

@mattab mattab closed this Oct 1, 2015

@diosmosis

This comment has been minimized.

Show comment
Hide comment
@diosmosis

diosmosis Oct 1, 2015

Member

@mattab Are you still going to apply my review?

Member

diosmosis commented Oct 1, 2015

@mattab Are you still going to apply my review?

@mattab mattab referenced this issue Oct 1, 2015

Merged

Segment subquery cache: minor code review items #8892

1 of 1 task complete
@mattab

This comment has been minimized.

Show comment
Hide comment
@mattab

mattab Oct 1, 2015

Member

Yes in #8892

Member

mattab commented Oct 1, 2015

Yes in #8892

mattab added a commit that referenced this issue Oct 4, 2015

@mattab mattab changed the title from Cache segment subqueries using LIKE constraint to Archiving performance: cache the slow segment subqueries that are using LIKE constraint Oct 13, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment