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

6 files changed, 96 insertions, 96 deletions

diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_day.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_day.xml
index 9d4fb1b262..ceefbebe55 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_day.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_day.xml
@@ -2,74 +2,74 @@
 <result>
 	<row>
 		<label>Electronics &amp; Cameras</label>
-		<revenue>15000</revenue>
-		<quantity>18</quantity>
+		<revenue>2500</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_uniq_visitors>2</nb_uniq_visitors>
 		<nb_visits>3</nb_visits>
 		<nb_actions>6</nb_actions>
-		<avg_price>6000</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>1000</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>66.67%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 1</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 2</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 4</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 5</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Product Category not defined</label>
-		<revenue>3667.32</revenue>
-		<quantity>42</quantity>
+		<revenue>611.22</revenue>
+		<quantity>7</quantity>
 		<orders>2</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>3</nb_visits>
 		<nb_actions>5</nb_actions>
-		<avg_price>333.66</avg_price>
-		<avg_quantity>21</avg_quantity>
+		<avg_price>55.61</avg_price>
+		<avg_quantity>3.5</avg_quantity>
 		<conversion_rate>66.67%</conversion_rate>
 	</row>
 	<row>
diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_week.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_week.xml
index 982f9a7258..23c73914bf 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_week.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsCategory_week.xml
@@ -2,74 +2,74 @@
 <result>
 	<row>
 		<label>Electronics &amp; Cameras</label>
-		<revenue>15000</revenue>
-		<quantity>18</quantity>
+		<revenue>2500</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_visits>3</nb_visits>
 		<nb_actions>6</nb_actions>
 		<sum_daily_nb_uniq_visitors>2</sum_daily_nb_uniq_visitors>
-		<avg_price>6000</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>1000</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>66.67%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 1</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 2</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 4</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Multiple Category 5</label>
-		<revenue>6000</revenue>
-		<quantity>12</quantity>
+		<revenue>1000</revenue>
+		<quantity>2</quantity>
 		<orders>1</orders>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>3000</avg_price>
-		<avg_quantity>12</avg_quantity>
+		<avg_price>500</avg_price>
+		<avg_quantity>2</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>Product Category not defined</label>
-		<revenue>3667.32</revenue>
-		<quantity>42</quantity>
+		<revenue>611.22</revenue>
+		<quantity>7</quantity>
 		<orders>2</orders>
 		<nb_visits>4</nb_visits>
 		<nb_actions>7</nb_actions>
 		<sum_daily_nb_uniq_visitors>2</sum_daily_nb_uniq_visitors>
-		<avg_price>333.66</avg_price>
-		<avg_quantity>21</avg_quantity>
+		<avg_price>55.61</avg_price>
+		<avg_quantity>3.5</avg_quantity>
 		<conversion_rate>50%</conversion_rate>
 	</row>
 	<row>
diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_day.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_day.xml
index 4d8563a99e..8beefcdba1 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_day.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_day.xml
@@ -2,32 +2,32 @@
 <result>
 	<row>
 		<label>Canon SLR</label>
-		<revenue>9000</revenue>
-		<quantity>6</quantity>
+		<revenue>1500</revenue>
+		<quantity>1</quantity>
 		<orders>1</orders>
-		<avg_price>9000</avg_price>
-		<avg_quantity>6</avg_quantity>
+		<avg_price>1500</avg_price>
+		<avg_quantity>1</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>
 		<label>PRODUCT name</label>
-		<revenue>6067.32</revenue>
-		<quantity>18</quantity>
+		<revenue>1011.22</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>2</nb_visits>
 		<nb_actions>5</nb_actions>
-		<avg_price>1533.66</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>255.61</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>PRODUCT name BIS</label>
-		<revenue>3600</revenue>
-		<quantity>36</quantity>
+		<revenue>600</revenue>
+		<quantity>6</quantity>
 		<orders>1</orders>
-		<avg_price>600</avg_price>
-		<avg_quantity>36</avg_quantity>
+		<avg_price>100</avg_price>
+		<avg_quantity>6</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>
diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_week.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_week.xml
index b413464bdb..2a5b5affd5 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_week.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsName_week.xml
@@ -2,32 +2,32 @@
 <result>
 	<row>
 		<label>Canon SLR</label>
-		<revenue>9000</revenue>
-		<quantity>6</quantity>
+		<revenue>1500</revenue>
+		<quantity>1</quantity>
 		<orders>1</orders>
-		<avg_price>9000</avg_price>
-		<avg_quantity>6</avg_quantity>
+		<avg_price>1500</avg_price>
+		<avg_quantity>1</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>
 		<label>PRODUCT name</label>
-		<revenue>6067.32</revenue>
-		<quantity>18</quantity>
+		<revenue>1011.22</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_visits>2</nb_visits>
 		<nb_actions>5</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>1533.66</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>255.61</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>PRODUCT name BIS</label>
-		<revenue>3600</revenue>
-		<quantity>36</quantity>
+		<revenue>600</revenue>
+		<quantity>6</quantity>
 		<orders>1</orders>
-		<avg_price>600</avg_price>
-		<avg_quantity>36</avg_quantity>
+		<avg_price>100</avg_price>
+		<avg_quantity>6</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>
diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_day.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_day.xml
index 02e9f0adf8..83b6b8ab73 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_day.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_day.xml
@@ -2,35 +2,35 @@
 <result>
 	<row>
 		<label>SKU2</label>
-		<revenue>9000</revenue>
-		<quantity>6</quantity>
+		<revenue>1500</revenue>
+		<quantity>1</quantity>
 		<orders>1</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
-		<avg_price>9000</avg_price>
-		<avg_quantity>6</avg_quantity>
+		<avg_price>1500</avg_price>
+		<avg_quantity>1</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>SKU VERY nice indeed</label>
-		<revenue>6067.32</revenue>
-		<quantity>18</quantity>
+		<revenue>1011.22</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_uniq_visitors>1</nb_uniq_visitors>
 		<nb_visits>3</nb_visits>
 		<nb_actions>8</nb_actions>
-		<avg_price>1533.66</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>255.61</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>66.67%</conversion_rate>
 	</row>
 	<row>
 		<label>ANOTHER SKU HERE</label>
-		<revenue>3600</revenue>
-		<quantity>36</quantity>
+		<revenue>600</revenue>
+		<quantity>6</quantity>
 		<orders>1</orders>
-		<avg_price>600</avg_price>
-		<avg_quantity>36</avg_quantity>
+		<avg_price>100</avg_price>
+		<avg_quantity>6</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>
diff --git a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_week.xml b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_week.xml
index 2ef4f0093f..c3fde5ca94 100644
--- a/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_week.xml
+++ b/plugins/Ecommerce/tests/System/expected/test_ecommerceOrderWithItems_SegmentPageUrlContains__Goals.getItemsSku_week.xml
@@ -2,35 +2,35 @@
 <result>
 	<row>
 		<label>SKU2</label>
-		<revenue>9000</revenue>
-		<quantity>6</quantity>
+		<revenue>1500</revenue>
+		<quantity>1</quantity>
 		<orders>1</orders>
 		<nb_visits>1</nb_visits>
 		<nb_actions>1</nb_actions>
 		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
-		<avg_price>9000</avg_price>
-		<avg_quantity>6</avg_quantity>
+		<avg_price>1500</avg_price>
+		<avg_quantity>1</avg_quantity>
 		<conversion_rate>100%</conversion_rate>
 	</row>
 	<row>
 		<label>SKU VERY nice indeed</label>
-		<revenue>6067.32</revenue>
-		<quantity>18</quantity>
+		<revenue>1011.22</revenue>
+		<quantity>3</quantity>
 		<orders>2</orders>
 		<nb_visits>4</nb_visits>
 		<nb_actions>10</nb_actions>
 		<sum_daily_nb_uniq_visitors>2</sum_daily_nb_uniq_visitors>
-		<avg_price>1533.66</avg_price>
-		<avg_quantity>9</avg_quantity>
+		<avg_price>255.61</avg_price>
+		<avg_quantity>1.5</avg_quantity>
 		<conversion_rate>50%</conversion_rate>
 	</row>
 	<row>
 		<label>ANOTHER SKU HERE</label>
-		<revenue>3600</revenue>
-		<quantity>36</quantity>
+		<revenue>600</revenue>
+		<quantity>6</quantity>
 		<orders>1</orders>
-		<avg_price>600</avg_price>
-		<avg_quantity>36</avg_quantity>
+		<avg_price>100</avg_price>
+		<avg_quantity>6</avg_quantity>
 		<conversion_rate>0%</conversion_rate>
 	</row>
 	<row>