Archiving performance: cache the slow segment subqueries that are using LIKE constraint #8850
Description
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.