Faster segment archiving (#14761)

* use temporary table when segmenting

* still apply original where

* do not apply where twice

* make sure to sort tables correctly

* performance tweak, no longer a where needed in most queries

* fix notice

* have a better cache per date

* small tweaks

* potentially fix an error where we received too many rows

* do not apply any custom force group by

* better handling of log tables to fix some tests

* enable new feature through config

* remove comment

* fix some system tests

* drop table before starting to archive in case archiving fails

* fix more tests

* trying to fix ui test

* save the config change

* use reader when executing ranking queries

* apply review feedback

* drop table only if it actually exists to prevent problems on a reader

* Update JoinGenerator.php

9 files changed, 302 insertions, 15 deletions

diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Actions.getPageUrls_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Actions.getPageUrls_day.xml
index c234bed59e..97b0082789 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Actions.getPageUrls_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Actions.getPageUrls_day.xml
@@ -1,2 +1,31 @@
 <?xml version="1.0" encoding="utf-8" ?>
-<result />
\ No newline at end of file
+<result>
+	<row>
+		<label>/page</label>
+		<nb_visits>2</nb_visits>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_hits>2</nb_hits>
+		<sum_time_spent>540</sum_time_spent>
+		<nb_hits_with_time_generation>2</nb_hits_with_time_generation>
+		<min_time_generation>0.333</min_time_generation>
+		<max_time_generation>0.333</max_time_generation>
+		<sum_bandwidth>0</sum_bandwidth>
+		<nb_hits_with_bandwidth>0</nb_hits_with_bandwidth>
+		<min_bandwidth />
+		<max_bandwidth />
+		<entry_nb_uniq_visitors>2</entry_nb_uniq_visitors>
+		<entry_nb_visits>2</entry_nb_visits>
+		<entry_nb_actions>2</entry_nb_actions>
+		<entry_sum_visit_length>542</entry_sum_visit_length>
+		<entry_bounce_count>2</entry_bounce_count>
+		<exit_nb_uniq_visitors>2</exit_nb_uniq_visitors>
+		<exit_nb_visits>2</exit_nb_visits>
+		<avg_bandwidth>0</avg_bandwidth>
+		<avg_time_on_page>270</avg_time_on_page>
+		<bounce_rate>100%</bounce_rate>
+		<exit_rate>100%</exit_rate>
+		<avg_time_generation>0.333</avg_time_generation>
+		<url>http://www.example.org/page</url>
+		<segment>pageUrl==http%3A%2F%2Fwww.example.org%2Fpage</segment>
+	</row>
+</result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentNames_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentNames_day.xml
index c234bed59e..aab05b8c9c 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentNames_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentNames_day.xml
@@ -1,2 +1,33 @@
 <?xml version="1.0" encoding="utf-8" ?>
-<result />
\ No newline at end of file
+<result>
+	<row>
+		<label>ImageAd</label>
+		<nb_uniq_visitors>8</nb_uniq_visitors>
+		<nb_visits>8</nb_visits>
+		<nb_impressions>8</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>25%</interaction_rate>
+		<contentTarget>http://www.example.com</contentTarget>
+		<segment>contentName==ImageAd</segment>
+	</row>
+	<row>
+		<label>Text Ad</label>
+		<nb_uniq_visitors>6</nb_uniq_visitors>
+		<nb_visits>6</nb_visits>
+		<nb_impressions>6</nb_impressions>
+		<nb_interactions>4</nb_interactions>
+		<interaction_rate>66.67%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
+		<segment>contentName==Text+Ad</segment>
+	</row>
+	<row>
+		<label>Video Ad</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentName==Video+Ad</segment>
+	</row>
+</result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentPieces_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentPieces_day.xml
index c234bed59e..ccdd21b1d9 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentPieces_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentInteractionMatch__Contents.getContentPieces_day.xml
@@ -1,2 +1,72 @@
 <?xml version="1.0" encoding="utf-8" ?>
-<result />
\ No newline at end of file
+<result>
+	<row>
+		<label>Click to download Piwik now</label>
+		<nb_uniq_visitors>4</nb_uniq_visitors>
+		<nb_visits>4</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>50%</interaction_rate>
+		<contentTarget>http://piwik.org/download</contentTarget>
+		<segment>contentPiece==Click+to+download+Piwik+now</segment>
+	</row>
+	<row>
+		<label>/path/ad.jpg</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>100%</interaction_rate>
+		<contentTarget>http://www.example.com</contentTarget>
+		<segment>contentPiece==%2Fpath%2Fad.jpg</segment>
+	</row>
+	<row>
+		<label>/path/ad2.jpg</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget>http://www.example.com</contentTarget>
+		<segment>contentPiece==%2Fpath%2Fad2.jpg</segment>
+	</row>
+	<row>
+		<label>Click NOW</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>100%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
+		<segment>contentPiece==Click+NOW</segment>
+	</row>
+	<row>
+		<label>Content Piece not defined</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+	</row>
+	<row>
+		<label>movie.mov</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentPiece==movie.mov</segment>
+	</row>
+	<row>
+		<label>Unknown</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentPiece==Unknown</segment>
+	</row>
+</result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Actions.getPageUrls_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Actions.getPageUrls_day.xml
index c234bed59e..97b0082789 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Actions.getPageUrls_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Actions.getPageUrls_day.xml
@@ -1,2 +1,31 @@
 <?xml version="1.0" encoding="utf-8" ?>
-<result />
\ No newline at end of file
+<result>
+	<row>
+		<label>/page</label>
+		<nb_visits>2</nb_visits>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_hits>2</nb_hits>
+		<sum_time_spent>540</sum_time_spent>
+		<nb_hits_with_time_generation>2</nb_hits_with_time_generation>
+		<min_time_generation>0.333</min_time_generation>
+		<max_time_generation>0.333</max_time_generation>
+		<sum_bandwidth>0</sum_bandwidth>
+		<nb_hits_with_bandwidth>0</nb_hits_with_bandwidth>
+		<min_bandwidth />
+		<max_bandwidth />
+		<entry_nb_uniq_visitors>2</entry_nb_uniq_visitors>
+		<entry_nb_visits>2</entry_nb_visits>
+		<entry_nb_actions>2</entry_nb_actions>
+		<entry_sum_visit_length>542</entry_sum_visit_length>
+		<entry_bounce_count>2</entry_bounce_count>
+		<exit_nb_uniq_visitors>2</exit_nb_uniq_visitors>
+		<exit_nb_visits>2</exit_nb_visits>
+		<avg_bandwidth>0</avg_bandwidth>
+		<avg_time_on_page>270</avg_time_on_page>
+		<bounce_rate>100%</bounce_rate>
+		<exit_rate>100%</exit_rate>
+		<avg_time_generation>0.333</avg_time_generation>
+		<url>http://www.example.org/page</url>
+		<segment>pageUrl==http%3A%2F%2Fwww.example.org%2Fpage</segment>
+	</row>
+</result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentNames_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentNames_day.xml
index f0c23afff4..aab05b8c9c 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentNames_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentNames_day.xml
@@ -2,12 +2,32 @@
 <result>
 	<row>
 		<label>ImageAd</label>
-		<nb_uniq_visitors>4</nb_uniq_visitors>
-		<nb_visits>4</nb_visits>
-		<nb_impressions>4</nb_impressions>
+		<nb_uniq_visitors>8</nb_uniq_visitors>
+		<nb_visits>8</nb_visits>
+		<nb_impressions>8</nb_impressions>
 		<nb_interactions>2</nb_interactions>
-		<interaction_rate>50%</interaction_rate>
+		<interaction_rate>25%</interaction_rate>
 		<contentTarget>http://www.example.com</contentTarget>
 		<segment>contentName==ImageAd</segment>
 	</row>
+	<row>
+		<label>Text Ad</label>
+		<nb_uniq_visitors>6</nb_uniq_visitors>
+		<nb_visits>6</nb_visits>
+		<nb_impressions>6</nb_impressions>
+		<nb_interactions>4</nb_interactions>
+		<interaction_rate>66.67%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
+		<segment>contentName==Text+Ad</segment>
+	</row>
+	<row>
+		<label>Video Ad</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentName==Video+Ad</segment>
+	</row>
 </result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentPieces_day.xml b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentPieces_day.xml
index ae70087a09..ccdd21b1d9 100644..100755
--- a/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentPieces_day.xml
+++ b/plugins/Contents/tests/System/expected/test_Contents_contentTargetMatch__Contents.getContentPieces_day.xml
@@ -1,6 +1,16 @@
 <?xml version="1.0" encoding="utf-8" ?>
 <result>
 	<row>
+		<label>Click to download Piwik now</label>
+		<nb_uniq_visitors>4</nb_uniq_visitors>
+		<nb_visits>4</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>50%</interaction_rate>
+		<contentTarget>http://piwik.org/download</contentTarget>
+		<segment>contentPiece==Click+to+download+Piwik+now</segment>
+	</row>
+	<row>
 		<label>/path/ad.jpg</label>
 		<nb_uniq_visitors>2</nb_uniq_visitors>
 		<nb_visits>2</nb_visits>
@@ -20,4 +30,43 @@
 		<contentTarget>http://www.example.com</contentTarget>
 		<segment>contentPiece==%2Fpath%2Fad2.jpg</segment>
 	</row>
+	<row>
+		<label>Click NOW</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>100%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
+		<segment>contentPiece==Click+NOW</segment>
+	</row>
+	<row>
+		<label>Content Piece not defined</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+	</row>
+	<row>
+		<label>movie.mov</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentPiece==movie.mov</segment>
+	</row>
+	<row>
+		<label>Unknown</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentPiece==Unknown</segment>
+	</row>
 </result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Actions.getPageUrls_day.xml b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Actions.getPageUrls_day.xml
index c234bed59e..97b0082789 100644..100755
--- a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Actions.getPageUrls_day.xml
+++ b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Actions.getPageUrls_day.xml
@@ -1,2 +1,31 @@
 <?xml version="1.0" encoding="utf-8" ?>
-<result />
\ No newline at end of file
+<result>
+	<row>
+		<label>/page</label>
+		<nb_visits>2</nb_visits>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_hits>2</nb_hits>
+		<sum_time_spent>540</sum_time_spent>
+		<nb_hits_with_time_generation>2</nb_hits_with_time_generation>
+		<min_time_generation>0.333</min_time_generation>
+		<max_time_generation>0.333</max_time_generation>
+		<sum_bandwidth>0</sum_bandwidth>
+		<nb_hits_with_bandwidth>0</nb_hits_with_bandwidth>
+		<min_bandwidth />
+		<max_bandwidth />
+		<entry_nb_uniq_visitors>2</entry_nb_uniq_visitors>
+		<entry_nb_visits>2</entry_nb_visits>
+		<entry_nb_actions>2</entry_nb_actions>
+		<entry_sum_visit_length>542</entry_sum_visit_length>
+		<entry_bounce_count>2</entry_bounce_count>
+		<exit_nb_uniq_visitors>2</exit_nb_uniq_visitors>
+		<exit_nb_visits>2</exit_nb_visits>
+		<avg_bandwidth>0</avg_bandwidth>
+		<avg_time_on_page>270</avg_time_on_page>
+		<bounce_rate>100%</bounce_rate>
+		<exit_rate>100%</exit_rate>
+		<avg_time_generation>0.333</avg_time_generation>
+		<url>http://www.example.org/page</url>
+		<segment>pageUrl==http%3A%2F%2Fwww.example.org%2Fpage</segment>
+	</row>
+</result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentNames_day.xml b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentNames_day.xml
index 1f75bfaa1b..aab05b8c9c 100644..100755
--- a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentNames_day.xml
+++ b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentNames_day.xml
@@ -12,12 +12,22 @@
 	</row>
 	<row>
 		<label>Text Ad</label>
-		<nb_uniq_visitors>4</nb_uniq_visitors>
-		<nb_visits>4</nb_visits>
-		<nb_impressions>4</nb_impressions>
-		<nb_interactions>2</nb_interactions>
-		<interaction_rate>50%</interaction_rate>
-		<contentTarget>http://piwik.org/download</contentTarget>
+		<nb_uniq_visitors>6</nb_uniq_visitors>
+		<nb_visits>6</nb_visits>
+		<nb_impressions>6</nb_impressions>
+		<nb_interactions>4</nb_interactions>
+		<interaction_rate>66.67%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
 		<segment>contentName==Text+Ad</segment>
 	</row>
+	<row>
+		<label>Video Ad</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentName==Video+Ad</segment>
+	</row>
 </result>
\ No newline at end of file
diff --git a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentPieces_day.xml b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentPieces_day.xml
index 98fbf6ad4a..ccdd21b1d9 100644..100755
--- a/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentPieces_day.xml
+++ b/plugins/Contents/tests/System/expected/test_ContentscontentNameOrPieceMatch__Contents.getContentPieces_day.xml
@@ -31,6 +31,16 @@
 		<segment>contentPiece==%2Fpath%2Fad2.jpg</segment>
 	</row>
 	<row>
+		<label>Click NOW</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>2</nb_impressions>
+		<nb_interactions>2</nb_interactions>
+		<interaction_rate>100%</interaction_rate>
+		<contentTarget>http://piwik.org/</contentTarget>
+		<segment>contentPiece==Click+NOW</segment>
+	</row>
+	<row>
 		<label>Content Piece not defined</label>
 		<nb_uniq_visitors>2</nb_uniq_visitors>
 		<nb_visits>2</nb_visits>
@@ -40,6 +50,16 @@
 		<contentTarget />
 	</row>
 	<row>
+		<label>movie.mov</label>
+		<nb_uniq_visitors>2</nb_uniq_visitors>
+		<nb_visits>2</nb_visits>
+		<nb_impressions>4</nb_impressions>
+		<nb_interactions>0</nb_interactions>
+		<interaction_rate>0%</interaction_rate>
+		<contentTarget />
+		<segment>contentPiece==movie.mov</segment>
+	</row>
+	<row>
 		<label>Unknown</label>
 		<nb_uniq_visitors>2</nb_uniq_visitors>
 		<nb_visits>2</nb_visits>