Creating a custom index in a multi-tenant database environment

• US 10,108,648 B2
• Filed: 07/13/2012
• Issued: 10/23/2018
• Est. Priority Date: 07/13/2011
• Status: Active Grant

• Current Assignee: Intel Corporation, Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Rajan, Chirag, Dutta, Arup, O'Brien, John, Bathija, Jaikumar, Salmon, Greg, Soble, Dan, Penmetsa, RamaLinga R., Kim, Hoon, Jiang, Yanan, Rajan, Karthik, Collins, Jesse
• Primary Examiner(s): Kim, Taelor
• Assistant Examiner(s): Frumkin, Jesse P
• Application Number:
  US13/549,306
• Publication Number:
  US 20130018890A1
• Time in Patent Office: 2,293 Days
• Field of Search: 707741
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2455 Query execution

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.

Claims

1. A method comprising:

monitoring queries that are applied to and running on a database;

flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;

evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows available to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on the elapsed time for the query;

ranking the candidate queries based on the evaluating;

selecting a candidate query based on the ranking from the evaluating;

utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and

creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

2. The method of claim 1, wherein a second of the criteria is scan size and wherein evaluating the candidate query comprises determining how many rows of the multi-tenant database are scanned by the candidate query and wherein selecting comprises selecting the candidate query if the number of rows exceeds a threshold.

3. The method of claim 1, wherein the selectivity criteria include determining how selective the candidate query is in scanning rows of the database and wherein selecting comprises selecting the candidate query if the number of rows and the selectivity score exceed thresholds.

4. The method of claim 3, wherein a third of the criteria is run time and wherein evaluating the candidate query comprises determining how much time the candidate query takes to run in the multi-tenant database and wherein selecting comprises selecting the candidate query if the number of rows, the selectivity, and the run time exceed thresholds.

5. The method of claim 1, wherein flagging comprises receiving a ranked set of queries and selecting a subset of the queries that have the highest rank.

6. The method of claim 5, wherein flagging comprises flagging a plurality of queries from the subset of the queries based on evaluating the queries of the subset of queries.

7. The method of claim 5, wherein selecting comprises selecting a subset of highest ranked queries for creating an index.

8. The method of claim 1, wherein creating the additional filter comprises

defining an index table including a tenant identifier for a tenant, a copy of data from a first data field having a first data type, a copy of data from a second data field having a second data type, and a key to the corresponding rows of the database;

and

sorting the index table based on the first data field and the second data field.

9. The method of claim 1, further comprising monitoring the created additional filter for frequency of use and removing the created additional filter if it is not used more frequently than a predefined number of times in a predefined duration of time.

10. A non-transitory machine-readable medium carrying one or more sequences of instructions for creating custom indexes in a database, the database being a multi-tenant database system, which instructions, when executed by one or more processors, cause the one or more processors to carry out the steps of:

monitoring queries that are applied to and running on a database;

flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;

evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows available to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on the elapsed time for the query;

ranking the candidate queries based on the evaluating;

selecting a candidate query based on the ranking from the evaluating;

utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and

creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

11. The medium of claim 10, wherein a second of the criteria is scan size, wherein the second of the criteria is selectivity, wherein evaluating the candidate query comprises determining how many rows of the multi-tenant database are scanned by the candidate query, determining how selective the candidate query is in scanning rows of the multi-tenant database, and wherein selecting comprises selecting the candidate query if the number of rows and the selectivity exceed a threshold.

12. The medium of claim 11, wherein flagging comprises receiving a set of queries ranked by runtime and selecting a subset of the queries that have the longest runtime.

13. The medium of claim 12, wherein flagging comprises excluding queries that include a null value from the selected queries.

14. An apparatus for creating custom indexes in a database, the database being a multi-tenant database, the apparatus comprising:

a hardware processor; and

one or more stored sequences of instructions which, when executed by the processor, cause the processor to carry out the steps of;

monitoring queries that are applied to and running on a database;

flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;

evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on factored by the elapsed time for the query;

ranking the candidate queries based on the evaluating;

selecting a candidate query based on the ranking from the evaluating;

utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and

creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

15. The apparatus of claim 14, wherein the step of creating the additional filter comprises:

defining an index table including a tenant identifier for a tenant, a copy of data from a first data field having a first data type, a copy of data from a second data field having a second data type, and a key to the corresponding rows of the multi-tenant data structure; and

sorting the index table based on the first data field and the second data field.

16. The apparatus of claim 15, wherein the index table is comprised of columns in a multi-tenant data database.

17. The apparatus of claim 15, wherein the index table is a separate data structure from the database.

18. The apparatus of claim 15, wherein one of the first data field and the second data field indicates a time.

19. The apparatus of claim 18, wherein the index table further comprises an index field.