Methods and systems for joining indexes for query optimization in a multi-tenant database

Patent metadata

Field	Value
Publication number	US-9405797-B2
Application number	US-201414222091-A
Country	US
Kind code	B2
Filing date	Mar 21, 2014
Priority date	Oct 5, 2009
Publication date	Aug 2, 2016
Grant date	Aug 2, 2016

Abstract

Methods and systems for query optimization for a multi-tenant database system are provided. Some embodiments comprise receiving at a network interface of a server in a multi-tenant database system an original query transmitted to the multi-tenant database system by a user associated with a tenant, wherein the original query is associated with data accessible by the tenant, and wherein the multi-tenant database system includes at least a first index and a second index. Metadata associated with the data is retrieved, wherein at least a portion of the data is stored in a common table within the multi-tenant database system. A tenant-selective query syntax is determined by analyzing at least one of metadata generated from information about the tenant or metadata generated from the data accessible by the tenant. An improved query is then generated using the query syntax, wherein the improved query is based at least in part upon the original query and a result of a join between a first number of rows associated with the first index and a second number of rows associated with the second index.

First claim

What is claimed is:

1. A computer-implemented method of improving a query, the method comprising:
receiving, with a server computing device, an original query transmitted by a remote computing device, wherein the original query is associated with data within a database, wherein data in the database has different characteristics for specific columns and at least one of the columns comprises information for tenant-specific filtering, and wherein the database includes at least a first index and a second index, wherein the first index is a standard index and wherein the second index is a custom index;
retrieving, using a processor of the server, tenant-level metadata associated with the data, wherein at least a portion of the data is stored in a common table within the database system;
scanning a first index column to identify a first set of rows, wherein the first index column is selected based on the original query;
scanning a second index column to identify a second set of rows, wherein the second index column is based on the original query;
analyzing, with the processor, metadata generated from tenant-level metadata generated from the data accessible by the group to determine a query syntax; and
generating, using the processor, an improved query using the query syntax, wherein the improved query is based at least in part upon the original query and a result of a join between a first number of rows associated with the first index and a second number of rows associated with the second index.

2. The method of claim 1 , further comprising:
receiving information identifying the user;
retrieving, using the processor, user-level metadata about the user; and
wherein determining, using the processor, comprises analyzing at least one of the group consisting of user-level metadata generated from information about the user, tenant-level metadata generated from information about the group, and metadata generated from the data accessible by the group.

3. The method of claim 1 , further comprising:
calculating selectivity for one or more columns of data accessible by the group; and
wherein determining, using a processor of the server, comprises analyzing the selectivity of the one or more columns of data accessible by the group.

4. The method of claim 1 , wherein the improved query includes a reference to a denormalized table, wherein the denormalized table comprises data accessible by the group.

5. The method of claim 4 , wherein the denormalized table comprises a search name lookup table.

6. The method of claim 4 , wherein the denormalized table comprises at least one index on a custom data field associated with the group.

7. The method of claim 4 , wherein the denormalized table comprises a custom foreign key column associated with the group.

8. The method of claim 1 , wherein the first and second indices are both standard indices.

9. The method of claim 1 , wherein the first and second indices are both standard b-tree indices stored in the common table.

10. The method of claim 1 , wherein the first index is a standard index and wherein the second index is a custom index.

11. The method of claim 10 , wherein the first index is stored in the common table, and wherein the second index is stored in a separate indexed table different than the common table.

12. The method of claim 10 , wherein the second number of rows associated with the second index is determined by joining a set of primary keys of the second index with a primary keys index of the common table.

13. A non-transitory computer-readable medium containing program code executable by a processor in a computer configurable to cause the computer to improve a query in a database, the program code including instructions to:
receiving, with a server computing device, an original query transmitted by a remote computing device, wherein the original query is associated with data within a database, wherein data in the database has different characteristics for specific columns and at least one of the columns comprises information for tenant-specific filtering, and wherein the database includes at least a first index and a second index, wherein the first index is a standard index and wherein the second index is a custom index;
retrieving, using a processor of the server, tenant-level metadata associated with the data, wherein at least a portion of the data is stored in a common table within the database system;
scanning a first index column to identify a first set of rows, wherein the first index column is selected based on the original query;
scanning a second index column to identify a second set of rows, wherein the second index column is based on the original query;
analyzing, with the processor, metadata generated from tenant-level metadata generated from the data accessible by the group to determine a query syntax; and
generating, using the processor, an improved query using the query syntax, wherein the improved query is based at least in part upon the original query and a result of a join between a first number of rows associated with the first index and a second number of rows associated with the second index.

14. The computer-readable medium of claim 13 , wherein the program code includes further instructions to:
receiving information identifying the user;
retrieving, using the processor, user-level metadata about the user; and
wherein determining, using the processor, comprises analyzing at least one of the group consisting of user-level metadata generated from information about the user, tenant-level metadata generated from information about the group, and metadata generated from the data accessible by the group.

15. The computer-readable medium of claim 13 , wherein the program code includes further instructions to:
calculating selectivity for one or more columns of data accessible by the group; and
wherein determining, using a processor of the server, comprises analyzing the selectivity of the one or more columns of data accessible by the group.

16. The computer-readable medium of claim 13 , wherein the improved query includes a reference to a denormalized table, wherein the denormalized table comprises data accessible by the group.

17. The computer-readable medium of claim 16 , wherein the denormalized table comprises a search name lookup table.

18. The computer-readable medium of claim 16 , wherein the denormalized table comprises at least one index on a custom data field associated with the group.

19. The computer-readable medium of claim 16 , wherein the denormalized table comprises a custom foreign key column associated with the group.

20. The computer-readable medium of claim 13 , wherein the first and second indices are both standard indices.

21. The computer-readable medium of claim 13 , wherein the first and second indices are both standard b-tree indices stored in the common table.

22. The computer-readable medium of claim 13 , wherein the first index is a standard index and wherein the second index is a custom index.

23. The computer-readable medium of claim 22 , wherein the first index is stored in the common table, and wherein the second index is stored in a separate indexed table different than the common table.

24. The computer-readable medium of claim 22 , wherein the second number of rows associated with the second index is determined by joining a set of primary keys of the second index with a primary keys index of the common table.

Assignees

• Salesforce Com Inc

Inventors

• Eidson William Charles
• Collins Jesse

Classifications

• G06F17/30536

  Physics · mapped topic

• G06F17/30463

  Physics · mapped topic

• G06F17/30466 Primary

  Physics · mapped topic

• G06F16/2462 Primary

  Approximate or statistical queries · CPC title

• G06F16/24544 Primary

  Join order optimisation · CPC title