Method, system, and computer program product for querying in a multi-tenant database
View Patent ↗More efficient querying of a multi-tenant database using dynamic tuning of database indices. A layer of meta-data associates data items with tenants, e.g., via tags, and the meta-data is used to optimize searches by channeling processing resources during a query to only those pieces of data bearing the relevant tenant's unique tag.
1. A method of querying in a multi-tenant database, the database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants, the method comprising:
generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;
receiving a SQL query; and
optimizing the SQL query based on the tenant-level statistics.
2. The method of claim 1 , wherein the plurality of tenants provide the tenant-specific data in one or more custom fields specific to each of the plurality of tenants.
3. The method of claim 2 , wherein the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.
4. The method of claim 2 wherein the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the at least one customizable field is indexable.
5. The method of claim 1 further comprising implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.
6. The method of claim 5 , wherein the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.
7. The method of claim 5 , wherein the at least one security mechanism includes encryption.
8. A multi-tenant database system comprising:
a database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants;
logic for generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;
logic for receiving a SQL query; and
logic for optimizing the SQL query based on the tenant-level statistics.
9. The system of claim 8 , wherein the system is operable such that the plurality of tenants provide the tenant-specific data in one or more custom fields that are specific to each of the plurality of tenants.
10. The system of claim 9 , wherein the system is operable such that the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.
11. The system of claim 9 wherein the system is operable such that the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the system is operable such that the at least one customizable field is indexable.
12. The system of claim 8 further comprising logic for implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.
13. The system of claim 12 , wherein the system is operable such that the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.
14. The system of claim 12 , wherein the system is operable such that the at least one security mechanism includes encryption.
15. A computer program product, comprising a non-transitory computer-readable medium having a computer readable program code embodied therein, the computer readable program code adapted to be executed by a processor to implement a method for querying in a multi-tenant database, the database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants, the method comprising:
generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;
receiving a SQL query; and
optimizing the SQL query based on the tenant-level statistics.
16. The computer program product of claim 15 , wherein the computer program product is operable such that the plurality of tenants provide the tenant-specific data in one or more custom fields that are specific to each of the plurality of tenants.
17. The computer program product of claim 16 , wherein the computer program product is operable such that the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.
18. The computer program product of claim 16 wherein the computer program product is operable such that the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the computer program product is operable such that the at least one customizable field is indexable.
19. The computer program product of claim 15 further comprising implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.
20. The computer program product of claim 19 , wherein the computer program product is operable such that the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.
21. The computer program product of claim 19 , wherein the computer program product is operable such that the at least one security mechanism includes encryption.