Awight has uploaded a new change for review.
https://gerrit.wikimedia.org/r/178569
Change subject: Chop out wildcard usages of civicrm_prevnext_cache
......................................................................
Chop out wildcard usages of civicrm_prevnext_cache
Sort of brute force approach. These queries were really expensive, and
for no reason. Just match the damn key.
Change-Id: I11aaedc570b6ba6cfa173c3ac07e83059a58904e
---
M CRM/Contact/Selector.php
M CRM/Core/BAO/PrevNextCache.php
2 files changed, 21 insertions(+), 21 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm/civicrm
refs/changes/69/178569/1
diff --git a/CRM/Contact/Selector.php b/CRM/Contact/Selector.php
index f998ee9..0902023 100644
--- a/CRM/Contact/Selector.php
+++ b/CRM/Contact/Selector.php
@@ -799,7 +799,7 @@
$sortByCharacter = CRM_Utils_Request::retrieve('sortByCharacter',
'String', CRM_Core_DAO::$_nullObject);
//for text field pagination selection save
- $countRow = CRM_Core_BAO_PrevNextCache::getCount("%civicrm search
{$cacheKey}%", NULL, "entity_table = 'civicrm_contact'", "LIKE");
+ $countRow = CRM_Core_BAO_PrevNextCache::getCount("civicrm search
{$cacheKey}", NULL, "entity_table = 'civicrm_contact'", '=');
if ((!$crmPID || $countRow == 0) && !$sortByCharacter) {
$this->fillupPrevNextCache($sort);
diff --git a/CRM/Core/BAO/PrevNextCache.php b/CRM/Core/BAO/PrevNextCache.php
index fd91000..7e9506e 100644
--- a/CRM/Core/BAO/PrevNextCache.php
+++ b/CRM/Core/BAO/PrevNextCache.php
@@ -116,8 +116,8 @@
}
if (isset($cacheKey)) {
- $sql .= " AND cacheKey LIKE %3";
- $params[3] = array("{$cacheKey}%", 'String');
+ $sql .= " AND cacheKey = %3";
+ $params[3] = array($cacheKey, 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
}
@@ -132,8 +132,8 @@
$params[3] = array($id2, 'Integer');
if (isset($cacheKey)) {
- $sql .= " AND cacheKey LIKE %4";
- $params[4] = array("{$cacheKey}%", 'String');
+ $sql .= " AND cacheKey = %4";
+ $params[4] = array($cacheKey, 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
@@ -217,8 +217,8 @@
}
// 1. Clear cache if any
- $sql = "DELETE FROM civicrm_prevnext_cache WHERE cacheKey LIKE %1";
- CRM_Core_DAO::executeQuery($sql, array(1 => array("{$cacheKeyString}%",
'String')));
+ $sql = "DELETE FROM civicrm_prevnext_cache WHERE cacheKey = %1";
+ CRM_Core_DAO::executeQuery($sql, array(1 => array($cacheKeyString,
'String')));
// FIXME: we need to start using temp tables / queries here instead of
arrays.
// And cleanup code in CRM/Contact/Page/DedupeFind.php
@@ -311,13 +311,13 @@
if (is_array($cIds)) {
$cIdFilter = "(" . implode(',', $cIds) . ")";
$whereClause = "
-WHERE cacheKey LIKE %1
+WHERE cacheKey = %1
AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
";
}
else {
$whereClause = "
-WHERE cacheKey LIKE %1
+WHERE cacheKey = %1
AND (entity_id1 = %2 OR entity_id2 = %2)
";
$params[2] = array("{$cIds}", 'Integer');
@@ -326,22 +326,22 @@
$whereClause .= "AND is_selected = 0";
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 1 " . $whereClause .
$entity_whereClause;
- $params[1] = array("%{$cacheKey}%", 'String');
+ $params[1] = array($cacheKey, 'String');
}
elseif ($action == 'unselect') {
$whereClause .= "AND is_selected = 1";
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 0 " . $whereClause .
$entity_whereClause;
- $params[1] = array("%{$cacheKey}%", 'String');
+ $params[1] = array($cacheKey, 'String');
}
// default action is reseting
}
elseif (!$cIds && $cacheKey && $action == 'unselect') {
$sql = "
UPDATE civicrm_prevnext_cache SET is_selected = 0
-WHERE cacheKey LIKE %1 AND is_selected = 1
+WHERE cacheKey = %1 AND is_selected = 1
" . $entity_whereClause;
- $params[1] = array("%{$cacheKey}%", 'String');
+ $params[1] = array($cacheKey, 'String');
}
CRM_Core_DAO::executeQuery($sql, $params);
}
@@ -366,12 +366,12 @@
$actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
$sql = "
SELECT entity_id1, entity_id2 FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1
+WHERE cacheKey = %1
$actionGet
$entity_whereClause
ORDER BY id
";
- $params[1] = array("%{$cacheKey}%", 'String');
+ $params[1] = array($cacheKey, 'String');
$contactIds = array($cacheKey => array());
$cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
@@ -390,10 +390,10 @@
$query = "
SELECT *
FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1 AND is_selected=1 AND cacheKey NOT LIKE %2
+WHERE cacheKey = %1 AND is_selected=1 AND cacheKey != %2
LIMIT $offset, $rowCount";
- $params1[1] = array("%{$cacheKey}%", 'String');
- $params1[2] = array("%{$cacheKey}_alphabet%", 'String');
+ $params1[1] = array($cacheKey, 'String');
+ $params1[2] = array($cacheKey . '_alphabet', 'String');
$dao = CRM_Core_DAO::executeQuery($query, $params1);
while ($dao->fetch()) {
$val[] = $dao->data;
@@ -418,9 +418,9 @@
$query = "
SELECT count(id)
FROM civicrm_prevnext_cache
-WHERE cacheKey LIKE %1 AND is_selected=1 AND cacheKey NOT LIKE %2";
- $params1[1] = array("%{$cacheKey}%", 'String');
- $params1[2] = array("%{$cacheKey}_alphabet%", 'String');
+WHERE cacheKey = %1 AND is_selected=1 AND cacheKey != %2";
+ $params1[1] = array($cacheKey, 'String');
+ $params1[2] = array($cacheKey . '_alphabet', 'String');
$paramsTotal = CRM_Core_DAO::singleValueQuery($query, $params1);
$params['total'] = $paramsTotal;
$obj->_pager = new CRM_Utils_Pager($params);
--
To view, visit https://gerrit.wikimedia.org/r/178569
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I11aaedc570b6ba6cfa173c3ac07e83059a58904e
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/crm/civicrm
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
