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

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.

1 - 20 . (canceled) 
     
     
         21 . A computer-implemented method for multitenant-aware query optimization, the method comprising:
 receiving, at a network interface of a server in a multitenant environment having a multitenant database system, an original query corresponding to a user associated with a tenant of the multitenant environment, wherein the original query is associated with data accessible by the tenant, and wherein at least one database column provides information on location of tenant data and the database provides a mapping table that can be customized by at least one user of the tenant;   retrieving, using a processor of the server, metadata accessible by the tenant in the multitenant database system, wherein the metadata has been generated based on tenant-specific information;   determining, using the processor, a filtered query by analyzing metadata generated from tenant-specific characteristics;   generating, using the processor, an improved query using the filtered query based on identified columns and mappings associated with the tenant, the retrieved metadata and a custom index; and   executing the improved query on data in the database identified columns and mappings associated with the tenant.   
     
     
         22 . The method of  claim 21  wherein the tenant-specific information comprises tenant-specific data statistics. 
     
     
         23 . The method of  claim 21  wherein the tenant-specific information comprises tenant usage statistics. 
     
     
         24 . The method of  claim 21  wherein the tenant-specific information comprises tenant-specific mappings utilized by a query optimizer. 
     
     
         25 . The method of  claim 21 , further comprising:
 receiving information identifying the user;   retrieving, using the processor, metadata about the user; and   analyzing metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant to generate the improved query.   
     
     
         26 . The method of  claim 21 , further comprising:
 calculating selectivity for one or more columns of data accessible by the tenant; and   analyzing the selectivity of the one or more columns of data accessible by the tenant to generate the improved query.   
     
     
         27 . The method of  claim 21 , wherein the improved query includes a reference to a denormalized table, wherein the denormalized table comprises data accessible by the tenant. 
     
     
         28 . The method of  claim 24 , wherein the denormalized table comprises a search name lookup table. 
     
     
         29 . The method of  claim 24 , wherein the denormalized table comprises at least one index on a custom data field associated with the tenant. 
     
     
         30 . The method of  claim 24 , wherein the denormalized table comprises a custom foreign key column associated with the tenant.