CiviCRM Community Forums (archive)

I have a custom search that I am working to optimize the speed. What I am noticing is that after the user clicks "submit", my SQL is executed twice.      Also, I am using "mytop" to monitor the queries, and I noticed there that my SQL seems to be run a third time within the context of " INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data   ..."  before the results are shown to the end-user.

So if the SQL takes X seconds to run, the user does not see any results until after X times 3 seconds.

Any ideas on how to get the SQL to be executed just once?

I found the source of the query being run one of the extra times: It was in the "count" function.   The custom searches that ship with CiviCRM share this behavior. (At least the few I checked. )

I read this article on enabling MySQL query caching: http://www.docplanet.org/mysql/mysql-query-cache-in-depth/ and am not sure if this is the best approach.     What would you suggest changing within CiviCRM itself to avoid rerunning the SQL for a custom search?   

I was looking at the "count" function, which does the following:
$sql = $this->all( );
           
           $dao = CRM_Core_DAO::executeQuery( $sql,
                                             CRM_Core_DAO::$_nullArray );
           return $dao->N;
Any ideas on how I can eliminate this extra execution of the SQL, but still get the count? ( Also, on eliminating the extra execution for the third call for the prev_next cache )