Changes to fix Spider Performance Bug MDEV-8954 in 10.1MDEV-8954

7 files changed, 221 insertions, 0 deletions

diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc
index 877e2621f5f..550981938d8 100644
--- a/storage/spider/ha_spider.cc
+++ b/storage/spider/ha_spider.cc
@@ -162,6 +162,7 @@ ha_spider::ha_spider(
   result_list.snap_direct_aggregate = FALSE;
 #endif
   result_list.direct_distinct = FALSE;
+  result_list.is_partitioned_config = FALSE;
   result_list.casual_read = NULL;
   result_list.use_both_key = FALSE;
   result_list.in_cmp_ref = FALSE;
@@ -270,6 +271,7 @@ ha_spider::ha_spider(
   result_list.snap_direct_aggregate = FALSE;
 #endif
   result_list.direct_distinct = FALSE;
+  result_list.is_partitioned_config = FALSE;
   result_list.casual_read = NULL;
   result_list.use_both_key = FALSE;
   result_list.in_cmp_ref = FALSE;
@@ -1604,6 +1606,7 @@ int ha_spider::reset()
   result_list.snap_direct_aggregate = FALSE;
 #endif
   result_list.direct_distinct = FALSE;
+  result_list.is_partitioned_config = FALSE;
   store_error_num = 0;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (
@@ -2109,6 +2112,7 @@ int ha_spider::index_read_map_internal(
   result_list.desc_flg = FALSE;
   result_list.sorted = TRUE;
   result_list.key_info = &table->key_info[active_index];
+  check_distinct_key_query();
   result_list.limit_num =
     result_list.internal_limit >= result_list.split_read ?
     result_list.split_read : result_list.internal_limit;
@@ -2425,6 +2429,7 @@ int ha_spider::pre_index_read_map(
   }
 #endif
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -2624,6 +2629,7 @@ int ha_spider::index_read_last_map_internal(
   result_list.desc_flg = TRUE;
   result_list.sorted = TRUE;
   result_list.key_info = &table->key_info[active_index];
+  check_distinct_key_query();
   result_list.limit_num =
     result_list.internal_limit >= result_list.split_read ?
     result_list.split_read : result_list.internal_limit;
@@ -2893,6 +2899,7 @@ int ha_spider::pre_index_read_last_map(
   DBUG_ENTER("ha_spider::pre_index_read_last_map");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -3088,6 +3095,7 @@ int ha_spider::index_first_internal(
     result_list.desc_flg = FALSE;
     result_list.sorted = TRUE;
     result_list.key_info = &table->key_info[active_index];
+    check_distinct_key_query();
     result_list.key_order = 0;
     result_list.limit_num =
       result_list.internal_limit >= result_list.split_read ?
@@ -3370,6 +3378,7 @@ int ha_spider::pre_index_first(
   DBUG_ENTER("ha_spider::pre_index_first");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -3472,6 +3481,7 @@ int ha_spider::index_last_internal(
     result_list.sorted = TRUE;
     result_list.key_info = &table->key_info[active_index];
     result_list.key_order = 0;
+    check_distinct_key_query();
     result_list.limit_num =
       result_list.internal_limit >= result_list.split_read ?
       result_list.split_read : result_list.internal_limit;
@@ -3753,6 +3763,7 @@ int ha_spider::pre_index_last(
   DBUG_ENTER("ha_spider::pre_index_last");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -3914,6 +3925,7 @@ int ha_spider::read_range_first_internal(
   result_list.desc_flg = FALSE;
   result_list.sorted = sorted;
   result_list.key_info = &table->key_info[active_index];
+  check_distinct_key_query();
   result_list.limit_num =
     result_list.internal_limit >= result_list.split_read ?
     result_list.split_read : result_list.internal_limit;
@@ -4186,6 +4198,7 @@ int ha_spider::pre_read_range_first(
   DBUG_ENTER("ha_spider::pre_read_range_first");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -5773,6 +5786,7 @@ int ha_spider::pre_multi_range_read_next(
   DBUG_ENTER("ha_spider::pre_multi_range_read_next");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -7654,6 +7668,7 @@ int ha_spider::pre_rnd_next(
   DBUG_ENTER("ha_spider::pre_rnd_next");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -8251,6 +8266,7 @@ int ha_spider::pre_ft_read(
   DBUG_ENTER("ha_spider::pre_ft_read");
   DBUG_PRINT("info",("spider this=%p", this));
   check_pre_call(use_parallel);
+  result_list.is_partitioned_config = TRUE;
   if (use_pre_call)
   {
     store_error_num =
@@ -12080,6 +12096,81 @@ void ha_spider::check_direct_order_limit()
   DBUG_VOID_RETURN;
 }
 
+/********************************************************************
+ * Check whether the current query is a SELECT DISTINCT using an
+ * index in a non-partitioned Spider configuration, with a
+ * projection list that consists solely of the first key prefix
+ * column.
+ *
+ * For a SELECT DISTINCT query using an index in a non-partitioned
+ * Spider configuration, with a projection list that consists
+ * solely of the first key prefix, set the internal row retrieval
+ * limit to avoid visiting each row multiple times.
+ ********************************************************************/
+void ha_spider::check_distinct_key_query()
+{
+    DBUG_ENTER( "ha_spider::check_distinct_key_query" );
+
+    if ( result_list.direct_distinct && !result_list.is_partitioned_config &&
+         result_list.keyread && result_list.check_direct_order_limit && eq_range )
+    {
+        // SELECT DISTINCT query using an index in a non-partitioned configuration
+        KEY_PART_INFO*  key_part = result_list.key_info->key_part;
+        Field*          key_field = key_part->field;
+
+        if ( is_sole_projection_field( key_field->field_index ) )
+        {
+            // Projection list consists solely of the first key prefix column
+
+            // Set the internal row retrieval limit to avoid visiting each row
+            // multiple times.  This fixes a Spider performance bug that
+            // caused each row to be visited multiple times.
+            result_list.internal_limit = 1;
+        }
+    }
+
+    DBUG_VOID_RETURN;
+}
+
+/********************************************************************
+ * Determine whether the current query's projection list
+ * consists solely of the specified column.
+ *
+ * Params   IN      - field_index:
+ *                    Field index of the column of interest within
+ *                    its table.
+ *
+ * Returns  TRUE    - if the query's projection list consists
+ *                    solely of the specified column.
+ *          FALSE   - otherwise.
+ ********************************************************************/
+bool ha_spider::is_sole_projection_field( uint16 field_index )
+{
+    // NOTE: It is assumed that spider_db_append_select_columns() has already been called
+    //       to build the bitmap of projection fields
+    bool                is_ha_sole_projection_field;
+    uint                loop_index, dbton_id;
+    spider_db_handler*  dbton_hdl;
+    DBUG_ENTER( "ha_spider::is_sole_projection_field" );
+
+    for ( loop_index = 0; loop_index < share->use_sql_dbton_count; loop_index++ )
+    {
+        dbton_id    = share->use_sql_dbton_ids[ loop_index ];
+        dbton_hdl   = dbton_handler[ dbton_id ];
+
+        if ( dbton_hdl->first_link_idx >= 0 )
+        {
+            is_ha_sole_projection_field = dbton_hdl->is_sole_projection_field( field_index );
+            if ( !is_ha_sole_projection_field )
+            {
+                DBUG_RETURN( FALSE );
+            }
+        }
+    }
+
+    DBUG_RETURN( TRUE );
+}
+
 int ha_spider::check_ha_range_eof()
 {
   DBUG_ENTER("ha_spider::check_ha_range_eof");
diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h
index e528b0e8a91..56bf14209b7 100644
--- a/storage/spider/ha_spider.h
+++ b/storage/spider/ha_spider.h
@@ -751,6 +751,8 @@ public:
   );
   uint check_partitioned();
   void check_direct_order_limit();
+  void check_distinct_key_query();
+  bool is_sole_projection_field( uint16 field_index );
   int check_ha_range_eof();
   int drop_tmp_tables();
   bool handler_opened(
diff --git a/storage/spider/spd_db_include.h b/storage/spider/spd_db_include.h
index 56dbc9ce0dc..30081a4625e 100644
--- a/storage/spider/spd_db_include.h
+++ b/storage/spider/spd_db_include.h
@@ -1279,6 +1279,9 @@ public:
     ulong sql_type,
     int link_idx
   ) = 0;
+  virtual bool is_sole_projection_field(
+      uint16 field_index
+  ) = 0;
   virtual bool is_bulk_insert_exec_period(
     bool bulk_end
   ) = 0;
@@ -1665,6 +1668,7 @@ typedef struct st_spider_result_list
   bool                    snap_direct_aggregate;
   SPIDER_DB_ROW           *snap_row;
 #endif
+  bool                    is_partitioned_config;
   bool                    in_cmp_ref;
   bool                    set_split_read;
   bool                    insert_dup_update_pushdown;
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index 3f56d9a9d89..f33c4672969 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -9516,6 +9516,65 @@ int spider_mysql_handler::append_explain_select(
   DBUG_RETURN(0);
 }
 
+/********************************************************************
+ * Determine whether the current query's projection list
+ * consists solely of the specified column.
+ *
+ * Params   IN      - field_index:
+ *                    Field index of the column of interest within
+ *                    its table.
+ *
+ * Returns  TRUE    - if the query's projection list consists
+ *                    solely of the specified column.
+ *          FALSE   - otherwise.
+ ********************************************************************/
+bool spider_mysql_handler::is_sole_projection_field( uint16 field_index )
+{
+    // Determine whether the projection list consists solely of the field of interest
+    bool            is_field_in_projection_list = FALSE;
+    TABLE*          table                       = spider->get_table();
+    uint16          projection_field_count      = 0;
+    uint16          projection_field_index;
+    Field**         field;
+    DBUG_ENTER( "spider_mysql_handler::is_sole_projection_field" );
+
+    for ( field = table->field; *field ; field++ )
+    {
+        projection_field_index = ( *field )->field_index;
+
+        if ( !( minimum_select_bit_is_set( projection_field_index ) ) )
+        {
+            // Current field is not in the projection list
+            continue;
+        }
+
+        projection_field_count++;
+
+        if ( !is_field_in_projection_list )
+        {
+            if ( field_index == projection_field_index )
+            {
+                // Field of interest is in the projection list
+                is_field_in_projection_list = TRUE;
+            }
+        }
+
+        if ( is_field_in_projection_list && ( projection_field_count != 1 ) )
+        {
+            // Field of interest is not the sole column in the projection list
+            DBUG_RETURN( FALSE );
+        }
+    }
+
+    if ( is_field_in_projection_list && ( projection_field_count == 1 ) )
+    {
+        // Field of interest is the only column in the projection list
+        DBUG_RETURN( TRUE );
+    }
+
+    DBUG_RETURN( FALSE );
+}
+
 bool spider_mysql_handler::is_bulk_insert_exec_period(
   bool bulk_end
 ) {
diff --git a/storage/spider/spd_db_mysql.h b/storage/spider/spd_db_mysql.h
index 48942d701dc..0619d8fde88 100644
--- a/storage/spider/spd_db_mysql.h
+++ b/storage/spider/spd_db_mysql.h
@@ -1128,6 +1128,9 @@ public:
     ulong sql_type,
     int link_idx
   );
+  bool is_sole_projection_field(
+      uint16 field_index
+  );
   bool is_bulk_insert_exec_period(
     bool bulk_end
   );
diff --git a/storage/spider/spd_db_oracle.cc b/storage/spider/spd_db_oracle.cc
index c8237f24f0f..f7959cfed29 100644
--- a/storage/spider/spd_db_oracle.cc
+++ b/storage/spider/spd_db_oracle.cc
@@ -9571,6 +9571,65 @@ int spider_oracle_handler::append_explain_select(
   DBUG_RETURN(0);
 }
 
+/********************************************************************
+ * Determine whether the current query's projection list
+ * consists solely of the specified column.
+ *
+ * Params   IN      - field_index:
+ *                    Field index of the column of interest within
+ *                    its table.
+ *
+ * Returns  TRUE    - if the query's projection list consists
+ *                    solely of the specified column.
+ *          FALSE   - otherwise.
+ ********************************************************************/
+bool spider_oracle_handler::is_sole_projection_field( uint16 field_index )
+{
+    // Determine whether the projection list consists solely of the field of interest
+    bool            is_field_in_projection_list = FALSE;
+    TABLE*          table                       = spider->get_table();
+    uint16          projection_field_count      = 0;
+    uint16          projection_field_index;
+    Field**         field;
+    DBUG_ENTER( "spider_oracle_handler::is_sole_projection_field" );
+
+    for ( field = table->field; *field; field++ )
+    {
+        projection_field_index = ( *field )->field_index;
+
+        if ( !( minimum_select_bit_is_set( projection_field_index ) ) )
+        {
+            // Current field is not in the projection list
+            continue;
+        }
+
+        projection_field_count++;
+
+        if ( !is_field_in_projection_list )
+        {
+            if (field_index == projection_field_index)
+            {
+                // Field of interest is in the projection list
+                is_field_in_projection_list     = TRUE;
+            }
+        }
+
+        if ( is_field_in_projection_list && ( projection_field_count != 1 ) )
+        {
+            // Field of interest is not the sole column in the projection list
+            DBUG_RETURN( FALSE );
+        }
+    }
+
+    if ( is_field_in_projection_list && ( projection_field_count == 1 ) )
+    {
+        // Field of interest is the only column in the projection list
+        DBUG_RETURN( TRUE );
+    }
+
+    DBUG_RETURN( FALSE );
+}
+
 bool spider_oracle_handler::is_bulk_insert_exec_period(
   bool bulk_end
 ) {
diff --git a/storage/spider/spd_db_oracle.h b/storage/spider/spd_db_oracle.h
index 05bea4d33ab..186c1bb9f37 100644
--- a/storage/spider/spd_db_oracle.h
+++ b/storage/spider/spd_db_oracle.h
@@ -1208,6 +1208,9 @@ public:
     ulong sql_type,
     int link_idx
   );
+  bool is_sole_projection_field(
+      uint16 field_index
+  );
   bool is_bulk_insert_exec_period(
     bool bulk_end
   );