Creating a custom index in a multi-tenant database environment

Abstract

Methods and systems are described for creating a custom index in a multi-tenant database environment. In one embodiment, a method includes obtaining query for a multi-tenant database that is recommended as a candidate for creating an additional filter, evaluating the query against criteria to determine whether to select the query for creating the additional filter, and creating the additional filter for the query, if the query is selected.

1 - 19 . (canceled) 
     
     
         20 . A method comprising:
 continuously monitoring queries that are executed on a database;   identifying a set of monitored queries that execute more slowly than others of the monitored queries, wherein the identified set of monitored queries are recommended as candidate queries for creating custom indexes to enhance performances associated with the candidate queries;   analyzing the candidate queries against criteria in a background process, wherein analyzing includes:   ranking the candidate queries based on the criteria;   selecting, based on the ranking, a candidate query to create a custom index that is optimal for current workloads;   finding a time window for enqueuing creation of the custom index;   upon reaching the time window, automatically creating the custom index for the selected candidate query using copies of data from the database as an index table for use by the selected candidate query when running on the database, wherein the custom index limits the selected candidate query to a subset of rows of the index table; and   after automatically creating the custom index, monitoring performance of the selected candidate query.   
     
     
         21 . The method of  claim 20 , wherein a first of the criteria comprises a score for selectivity, the score being a ratio of a number of rows available to the candidate query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on an elapsed time for the candidate query. 
     
     
         22 . The method of  claim 21 , wherein the selectivity refers to determining how selective the candidate query is in scanning rows of the database and selecting the candidate query if the number of rows and the selectivity score exceed a threshold. 
     
     
         23 . The method of  claim 20 , wherein a second of the criteria comprise a scan size and determining how many rows of the multi-tenant database are scanned by the candidate query and selecting the candidate query if the number of rows exceeds the threshold. 
     
     
         24 . The method of  claim 20 , wherein a third of the criteria comprises a run time and determining how much time the candidate query takes to run in a multi-tenant database and selecting the candidate query if the number of rows, the selectivity, and the run time exceed the threshold. 
     
     
         25 . The method of  claim 20 , further comprising receiving a ranked set of queries and selecting a subset of the monitored queries having a highest rank, wherein identifying further comprises identifying and indexing the subset of the monitored queries. 
     
     
         26 . The method of  claim 20 , wherein the custom index is created and applied based on predetermined recommendations to manage the monitored queries to achieve high performance of the database and optimization of the current workloads. 
     
     
         27 . A database system comprising:
 a data processing system having one or more processors and a memory coupled to the one or more processors, the one or more processors to:   continuously monitor queries that are executed on a database;   identify a set of monitored queries that execute more slowly than others of the monitored queries, wherein the identified set of monitored queries are recommended as candidate queries for creating custom indexes to enhance performances associated with the candidate queries;   analyze the candidate queries against criteria in a background process, wherein analyzing includes:   rank the candidate queries based on the criteria;   select, based on the ranking, a candidate query to create a custom index that is optimal for current workloads;   find a time window for enqueuing creation of the custom index;   upon reaching the time window, automatically create the custom index for the selected candidate query using copies of data from the database as an index table for use by the selected candidate query when running on the database, wherein the custom index limits the selected candidate query to a subset of rows of the index table; and   after automatically creating the custom index, monitor performance of the selected candidate query.   
     
     
         28 . The system of  claim 27 , wherein a first of the criteria comprises a score for selectivity, the score being a ratio of a number of rows available to the candidate query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on an elapsed time for the candidate query. 
     
     
         29 . The system of  claim 28 , wherein the selectivity refers to determining how selective the candidate query is in scanning rows of the database and selecting the candidate query if the number of rows and the selectivity score exceed a threshold. 
     
     
         30 . The system of  claim 27 , wherein a second of the criteria comprise a scan size and determining how many rows of the multi-tenant database are scanned by the candidate query and selecting the candidate query if the number of rows exceeds the threshold. 
     
     
         31 . The system of  claim 27 , wherein a third of the criteria comprises a run time and determining how much time the candidate query takes to run in a multi-tenant database and selecting the candidate query if the number of rows, the selectivity, and the run time exceed the threshold. 
     
     
         32 . The system of  claim 27 , wherein the one or more processors to receive a ranked set of queries and selecting a subset of the monitored queries having a highest rank, wherein identifying further comprises identifying and indexing the subset of the monitored queries. 
     
     
         33 . The system of  claim 27 , wherein the custom index is created and applied based on predetermined recommendations to manage the monitored queries to achieve high performance of the database and optimization of the current workloads. 
     
     
         34 . At least one machine-readable medium having stored thereon instructions which, when executed by a machine, cause the machine to perform operations comprising:
 continuously monitoring queries that are executed on a database;   identifying a set of monitored queries that execute more slowly than others of the monitored queries, wherein the identified set of monitored queries are recommended as candidate queries for creating custom indexes to enhance performances associated with the candidate queries;   analyzing the candidate queries against criteria in a background process, wherein analyzing includes:   ranking the candidate queries based on the criteria;   selecting, based on the ranking, a candidate query to create a custom index that is optimal for current workloads;   finding a time window for enqueuing creation of the custom index;   upon reaching the time window, automatically creating the custom index for the selected candidate query using copies of data from the database as an index table for use by the selected candidate query when running on the database, wherein the custom index limits the selected candidate query to a subset of rows of the index table; and   after automatically creating the custom index, monitoring performance of the selected candidate query.   
     
     
         35 . The machine-readable medium of  claim 34 , wherein a first of the criteria comprises a score for selectivity, the score being a ratio of a number of rows available to the candidate query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on an elapsed time for the candidate query. 
     
     
         36 . The machine-readable medium of  claim 35 , wherein the selectivity refers to determining how selective the candidate query is in scanning rows of the database and selecting the candidate query if the number of rows and the selectivity score exceed a threshold. 
     
     
         37 . The machine-readable medium of  claim 34 , wherein a second of the criteria comprise a scan size and determining how many rows of the multi-tenant database are scanned by the candidate query and selecting the candidate query if the number of rows exceeds the threshold. 
     
     
         38 . The machine-readable medium of  claim 34 , wherein a third of the criteria comprises a run time and determining how much time the candidate query takes to run in a multi-tenant database and selecting the candidate query if the number of rows, the selectivity, and the run time exceed the threshold. 
     
     
         39 . The machine-readable medium of  claim 34 , wherein the operations further comprise receiving a ranked set of queries and selecting a subset of the monitored queries having a highest rank, wherein identifying further comprises identifying and indexing the subset of the monitored queries, wherein the custom index is created and applied based on predetermined recommendations to manage the monitored queries to achieve high performance of the database and optimization of the current workloads.