Skip to content
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

Jump to bottom

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

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

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

quba opened this issue Sep 24, 2015 · 11 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone
2.15.0

Comments

@quba
Copy link
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
Copy link
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
Copy link
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
Copy link
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
Copy link
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

This was referenced Sep 28, 2015
Closed
Merged
@mattab
Copy link
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 mattab added Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. c: Performance For when we could improve the performance / speed of Matomo. labels Sep 29, 2015
@mattab
Copy link
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
Copy link
Contributor Author

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
Copy link
Contributor Author

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
Copy link
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 as completed Oct 1, 2015
@diosmosis
Copy link
Member

@mattab Are you still going to apply my review?

@mattab mattab mentioned this issue Oct 1, 2015
Merged
1 task
@mattab
Copy link
Member

mattab commented Oct 1, 2015

Yes in #8892

mattab added a commit that referenced this issue Oct 4, 2015
@mattab
Moved dependencies into DI
5eeb40a 
#8850
@mattab mattab mentioned this issue Oct 9, 2015
Closed
@mattab mattab changed the title Cache segment subqueries using LIKE constraint Archiving performance: cache the slow segment subqueries that are using LIKE constraint Oct 13, 2015
@tsteur tsteur mentioned this issue Jul 28, 2022
Open
@tsteur tsteur mentioned this issue Oct 21, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
c: Performance For when we could improve the performance / speed of Matomo. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Milestone
2.15.0
Development

No branches or pull requests
3 participants
@diosmosis @mattab @quba