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

• US 9,946,751 B2
• Filed: 07/15/2016
• Issued: 04/17/2018
• Est. Priority Date: 10/05/2009
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Eidson, William Charles, Collins, Jesse
• Primary Examiner(s): Nguyen, Phong H
• Application Number:
  US15/212,115
• Publication Number:
  US 20170017690A1
• Time in Patent Office: 641 Days
• Field of Search: 707713, 707714, 707715, 707741, 707759
• CPC Class Codes: G06F 16/2246 Trees, e.g. B+trees
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

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.

Claims

1. A computer-implemented method of improving a query that is to be performed in the context of a tenant-specific filter on a tenant identifier, the method comprising:

receiving at a network interface of a server an original query, wherein the original query is associated with data within a database as identified by the tenant identifier, and wherein the database includes at least a first index and a second index, further wherein the database stores data for multiple tenants that are unrelated organizations and have different database schemas, the data corresponding to each tenant having tenant-specific characteristics;

retrieving, using a processor of the server, metadata associated with the data, wherein at least a portion of the data is stored in a common table within the database system;

determining, using the processor, a tenant-selective query syntax by analyzing at least metadata generated from tenant-specific characteristics;

analyzing, using the processor, metadata generated from information about the organization to which the original query corresponds; 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 and the analysis of the metadata.

2. The method of claim 1, further comprising:

receiving information identifying the user;

retrieving, using the processor, metadata about the user; and

wherein determining, using the processor, comprises analyzing at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

3. The method of claim 1, further comprising:

calculating selectivity for one or more columns of data accessible by the tenant; and

wherein determining, using a processor of the server, comprises analyzing the selectivity of the one or more columns of data accessible by the tenant.

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 tenant.

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 tenant.

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

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 multi-tenant database system that is to be performed in the context of a tenant-specific filter on a tenant identifier, the program code including instructions to:

receiving at a network interface of a server an original query, wherein the original query is associated with data within a database as identified by the tenant identifier, and wherein the database includes at least a first index and a second index, further wherein the database stores data for multiple tenants that are unrelated organizations and have different database schemas, the data corresponding to each tenant having tenant-specific characteristics;

retrieving, using a processor of the server, metadata associated with the data, wherein at least a portion of the data is stored in a common table within the database system;

determining, using the processor, a tenant-selective query syntax by analyzing at least metadata generated from tenant-specific characteristics;

analyzing, using the processor, metadata generated from information about the organization to which the original query corresponds; 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 and the analysis of the metadata.

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, metadata about the user; and

wherein determining, using the processor, comprises analyzing at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

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 tenant; and

wherein determining, using a processor of the server, comprises analyzing the selectivity of the one or more columns of data accessible by the tenant.

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 tenant.

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 tenant.

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

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 13, 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.

25. A system for improving a query in a multi-tenant database system that is to be performed in the context of a tenant-specific filter on a tenant identifier, the system comprising:

a processor;

a network interface; and

a computer-readable medium tangibly embodied with instructions capable of configuring the processor to;

receive at a network interface of a server an original query, wherein the original query is associated with data within a database as identified by the tenant identifier, and wherein the database includes at least a first index and a second index, further wherein the database stores data for multiple tenants that are unrelated organizations and have different database schemas, the data corresponding to each tenant having tenant-specific characteristics;

retrieve, using a processor of the server, metadata associated with the data, wherein at least a portion of the data is stored in a common table within the database system;

determine, using the processor, a tenant-selective query syntax by analyzing at least metadata generated from tenant-specific characteristics;

analyze, using the processor, metadata generated from information about the organization to which the original query corresponds; and

generate, 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 and the analysis of the metadata.

26. The system of claim 25, wherein the computer-readable medium is tangibly embodied with further instructions to:

receiving information identifying the user;

retrieving, using the processor, metadata about the user; and

wherein determining, using the processor, comprises analyzing at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

27. The system of claim 25, wherein the computer-readable medium is tangibly embodied with further instructions to:

calculating selectivity for one or more columns of data accessible by the tenant; and

wherein determining, using a processor of the server, comprises analyzing the selectivity of the one or more columns of data accessible by the tenant.

28. The system of claim 25, wherein the improved query includes a reference to a denormalized table, wherein the denormalized table comprises data accessible by the tenant.

29. The system of claim 28, wherein the denormalized table comprises a search name lookup table.

30. The system of claim 28, wherein the denormalized table comprises at least one index on a custom data field associated with the tenant.

31. The system of claim 28, wherein the denormalized table comprises a custom foreign key column associated with the tenant.

32. The system of claim 25, wherein the first and second indices are both standard indices.

33. The system of claim 25, wherein the first and second indices are both standard b-tree indices stored in the common table.

34. The system of claim 25, wherein the first index is a standard index and wherein the second index is a custom index.

35. The system of claim 34, 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.

36. The system of claim 34, 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.

37. A computer-implemented method of improving a query that is to be performed in the context of a tenant-specific filter on a tenant identifier, the method comprising:

receiving at a network interface of a server an original query, wherein the original query is associated with data within a database as identified by the tenant identifier, and wherein the database includes at least a first index and a second index, further wherein the database stores data for multiple tenants that are unrelated organizations and have different database schemas, the data corresponding to each tenant having tenant-specific characteristics;

retrieving metadata associated with the data accessible by the tenant in a multi-tenant database system, wherein at least a portion of the data accessible by the tenant is stored in a common table within the multi-tenant database system;

scanning, using a processor of the server, a first index column to identify a first set of rows, wherein the first index column is selected based on the original query;

scanning, using the processor, a second index column to identify a second set of rows, wherein the second index column is selected based on the original query;

determining, using the processor, an intersection of the first set of rows and the second set of rows to identify a third set of rows;

determining, using the processor, a tenant-selective query syntax by analyzing at least metadata generated from tenant-specific characteristics;

analyzing, using the processor, metadata generated from information about the organization to which the original query corresponds; 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 and the analysis of the metadata.

38. The method of claim 37, further comprising:

receiving information identifying the user;

retrieving, using the processor, metadata about the user; and

wherein analyzing, using a processor of the server, comprises analyzing at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

39. The method of claim 37, wherein each tenant is only able to access data associated with that tenant.

40. The method of claim 37, wherein generating, using the processor, an improved query using the query syntax includes scanning the common table using the third set of rows.

41. The method of claim 37, wherein the first and second indices are both standard indices.

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

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

44. The method of claim 37, wherein the first index column is a standard index stored in the common table and wherein the second index column is a custom index stored in a separate indexed table different than the common table.

45. The method of claim 44, wherein the second number of rows of the second custom index are determined by joining a set of primary keys of the second index with a primary keys index of the common table.