chandrashekhar_srijan's sandbox: High water mark query parameter

Purpose

This module provides a simple interface with listing to add/edit the parameter added to a complex query for boosting the query performance.

This way a large number of row is simply skipped while fetching the records, results in performance gain by 80%-95% with any kind of complex query.

Best Usage

This is a useful module for top "x", most recent "x", type of queries.

Often sites show latest few records on their home page. Such queries normally process thousands of records to return just 5-20 records. Such complex queries are best suited to utilize this feature.

How to mark/obtain High water mark parameter

1. Original Query : pass "%s" in where caluse
2. Query Parameter: pass "and 1=1" initially
3. Query Fields: set exclusively the fields which are controlling the query. Rows with these fields value will be stored.
4. Save the parameter
5. See the listing
6. Find the minimum value of controlling field from the result set shown. For now it is "nid"
7. Find the minumum value of nid
8. Now you have got minimum value of nid as 504 or some value
9. Click on the Modify Link
10. Query Parameter: set "and n.nid> 503"
11. Save the parameter

This way nid, uid, tid or based on the type/nature of the query, the "Query Parameter" can be defined for the high water marking.
This is important step in defining the High water mark query parameter.

How to define a parameter

Following are the integral part of the High water mark parameters definition:

1. Name: have a unique name for query parameter
2. Query Parameter: place a query for the parameter
3. Original Query: have original query
4. Status: set the status for the inclusion or exclusion of parameter from the usage

When the parameter is being created, the "Query Parameter" gets inserted into the body of the "Original Query". If row(s) returns, the parameter will be created with the result set, otherwise error will be flagged.

This way interface allows to update the "Query Parameter" and run the test against the "Original Query". If the difference is too high, change the value of the "Query Parameter".

How to use a defined parameter

1. Call the function high_water_mark_query_parameter_get in the custom query
2. Pass the "Name" of the High Water Mark Query Parameter

If the "parameter" exists and its status is enabled, the additional query part added with "Query Parameter" is returned by the function.

Example

$query="select * from node n where n.status=1 %s order by n.created desc limit 3";
$result = db_query($query, high_water_mark_query_parameter_get('fetch_latest_3'));

The "Query Parameter" is controlled from the interface and update to this could be made as the high water mark goes up.

For detailed description of its usage and definition, please refer README.txt file.

Roadmap

Sponsorship and Development