Methods and systems for optimizing queries in a multi-tenant store

A non-transitory computer readable storage medium having instructions stored thereon that, when executed by processor in a host system, perform a method comprising:

• receiving a request at a host system for the multi-tenant database system, the request specifying data to be retrieved from the multi-tenant database system;
• retrieving one or more locations of the data to be retrieved based on the request;
• generating a database query based on the request, wherein the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store;
• optimizing the database query to generate an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query;
• and
• executing the optimized database query against the multi-tenant database system to retrieve the data.

A system comprising:

• a processor and a memory;
• a communications interface to a multi-tenant database system having a relational data store and a non-relational data store implemented therein;
• a request processor to receive a request specifying data to be retrieved from the multi-tenant database system;
• a customer schema processor to retrieve, based on the request, one or more locations of the data to be retrieved;
• a query layer agent to generate a database query based on the request, wherein the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store of the multi-tenant database system and one or more other data elements residing within the relational data store of the multi-tenant database system;
• a query optimizer to optimize the database query to generate an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query;
• and
• wherein the query layer agent to execute the optimized database query against the multi-tenant database system to retrieve the data.