Method, system, and computer program product for optimizing a database query
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 optimizing database queries 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 database query; and
optimizing the database 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 system of optimizing database queries in a multi-tenant database system comprising:
a memory coupled to a processor;
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 database query; and
logic for optimizing the database 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 embodied on a non-transitory storage medium of optimizing database queries in a multi-tenant database system, 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 computer program product comprising:
computer code 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;
computer code for receiving a database query; and
computer code for optimizing the database 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 computer code for 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.