diff options
author | matt <matt@59fd770c-687e-43c8-a1e3-f5a4ff64c105> | 2010-04-13 14:06:28 +0400 |
---|---|---|
committer | matt <matt@59fd770c-687e-43c8-a1e3-f5a4ff64c105> | 2010-04-13 14:06:28 +0400 |
commit | b2b5513a7792264b612a7476c0848240c7436766 (patch) | |
tree | d72194bc4d2f3707e41466bcd240bd0f2ce446fe /plugins/Live | |
parent | 15ae020f6a63293deb9e2a3e9f6a831b22965dfa (diff) |
Refs #1120
* Fixes performance issue with the Live! widget by changing the primary key on the log_visit table: all sql queries must hit the idsite first, then the idvisit, to ensure the INDEX is used for the ORDER BY LIMIT (without doing full table scan).
* Lowering timeout to 8s for more real time results
Diffstat (limited to 'plugins/Live')
-rw-r--r-- | plugins/Live/API.php | 48 | ||||
-rw-r--r-- | plugins/Live/templates/index.tpl | 2 | ||||
-rw-r--r-- | plugins/Live/templates/lastVisits.tpl | 2 |
3 files changed, 26 insertions, 26 deletions
diff --git a/plugins/Live/API.php b/plugins/Live/API.php index fc3a14a878..230559f4bc 100644 --- a/plugins/Live/API.php +++ b/plugins/Live/API.php @@ -142,21 +142,21 @@ class Piwik_Live_API $idvisit = $visitorDetailsArray['idVisit']; $sql = " - SELECT DISTINCT `" .Piwik::prefixTable('log_action')."`.`name` AS pageUrl - FROM `" .Piwik::prefixTable('log_link_visit_action')."` - INNER JOIN `" .Piwik::prefixTable('log_action')."` - ON `" .Piwik::prefixTable('log_link_visit_action')."`.`idaction_url` = `" .Piwik::prefixTable('log_action')."`.`idaction` - WHERE `" .Piwik::prefixTable('log_link_visit_action')."`.`idvisit` = $idvisit; + SELECT DISTINCT " .Piwik::prefixTable('log_action').".name AS pageUrl + FROM " .Piwik::prefixTable('log_link_visit_action')." + INNER JOIN " .Piwik::prefixTable('log_action')." + ON " .Piwik::prefixTable('log_link_visit_action').".idaction_url = " .Piwik::prefixTable('log_action').".idaction + WHERE " .Piwik::prefixTable('log_link_visit_action').".idvisit = $idvisit; "; $visitorDetailsArray['actionDetails'] = Piwik_FetchAll($sql); $sql = " - SELECT DISTINCT `" .Piwik::prefixTable('log_action')."`.`name` AS pageUrl - FROM `" .Piwik::prefixTable('log_link_visit_action')."` - INNER JOIN `" .Piwik::prefixTable('log_action')."` - ON `" .Piwik::prefixTable('log_link_visit_action')."`.`idaction_name` = `" .Piwik::prefixTable('log_action')."`.`idaction` - WHERE `" .Piwik::prefixTable('log_link_visit_action')."`.`idvisit` = $idvisit; + SELECT DISTINCT " .Piwik::prefixTable('log_action').".name AS pageUrl + FROM " .Piwik::prefixTable('log_link_visit_action')." + INNER JOIN " .Piwik::prefixTable('log_action')." + ON " .Piwik::prefixTable('log_link_visit_action').".idaction_name = " .Piwik::prefixTable('log_action').".idaction + WHERE " .Piwik::prefixTable('log_link_visit_action').".idvisit = $idvisit; "; $visitorDetailsArray['actionDetailsTitle'] = Piwik_FetchAll($sql); @@ -195,15 +195,16 @@ class Piwik_Live_API } $sql = "SELECT " . Piwik::prefixTable('log_visit') . ".* , - " . Piwik::prefixTable ( 'goal' ) . ".`match_attribute` + " . Piwik::prefixTable ( 'goal' ) . ".match_attribute FROM " . Piwik::prefixTable('log_visit') . " LEFT JOIN ".Piwik::prefixTable('log_conversion')." - ON " . Piwik::prefixTable('log_visit') . ".`idvisit` = " . Piwik::prefixTable('log_conversion') . ".`idvisit` + ON " . Piwik::prefixTable('log_visit') . ".idvisit = " . Piwik::prefixTable('log_conversion') . ".idvisit LEFT JOIN ".Piwik::prefixTable('goal')." - ON " . Piwik::prefixTable('goal') . ".`idgoal` = " . Piwik::prefixTable('log_conversion') . ".`idgoal` - AND " . Piwik::prefixTable('goal') . ".`deleted` = 0 + ON (" . Piwik::prefixTable('goal') . ".idsite = " . Piwik::prefixTable('log_visit') . ".idsite + AND " . Piwik::prefixTable('goal') . ".idgoal = " . Piwik::prefixTable('log_conversion') . ".idgoal) + AND " . Piwik::prefixTable('goal') . ".deleted = 0 $sqlWhere - ORDER BY idvisit DESC + ORDER BY idsite,idvisit DESC LIMIT $limit"; return Piwik_FetchAll($sql, $whereBind); @@ -223,19 +224,19 @@ class Piwik_Live_API { $where = $whereBind = array(); - $where[] = " " . Piwik::prefixTable('log_visit') . ".`idsite` = ? "; + $where[] = " " . Piwik::prefixTable('log_visit') . ".idsite = ? "; $whereBind[] = $idSite; if($minutes != 0) { $timeLimit = mktime(date("H"), date("i") - $minutes, 0, date("m"), date("d"), date("Y")); - $where[] = " `visit_last_action_time` > '".date('Y-m-d H:i:s',$timeLimit)."'"; + $where[] = " visit_last_action_time > '".date('Y-m-d H:i:s',$timeLimit)."'"; } if($days != 0) { $timeLimit = mktime(0, 0, 0, date("m"), date("d") - $days + 1, date("Y")); - $where[] = " `visit_last_action_time` > '".date('Y-m-d H:i:s', $timeLimit)."'"; + $where[] = " visit_last_action_time > '".date('Y-m-d H:i:s', $timeLimit)."'"; } $sqlWhere = ""; @@ -250,16 +251,15 @@ class Piwik_Live_API $sql = "SELECT " . Piwik::prefixTable('log_visit') . ".idvisit FROM " . Piwik::prefixTable('log_visit') . " $sqlWhere - ORDER BY idvisit DESC"; + ORDER BY idsite,idvisit DESC"; } // Pages elseif($type == self::TYPE_FETCH_PAGEVIEWS) { - $sql_select = "SELECT " . Piwik::prefixTable('log_link_visit_action') . ".`idaction_url`"; - $sql = $sql_select." - FROM " . Piwik::prefixTable('log_link_visit_action') . " - INNER JOIN " . Piwik::prefixTable('log_visit') . " - ON " . Piwik::prefixTable('log_visit') . ".`idvisit` = " . Piwik::prefixTable('log_link_visit_action') . ".`idvisit` + $sql = "SELECT " . Piwik::prefixTable('log_link_visit_action') . ".idaction_url + FROM " . Piwik::prefixTable('log_link_visit_action') . " + INNER JOIN " . Piwik::prefixTable('log_visit') . " + ON " . Piwik::prefixTable('log_visit') . ".idvisit = " . Piwik::prefixTable('log_link_visit_action') . ".idvisit $sqlWhere"; } else diff --git a/plugins/Live/templates/index.tpl b/plugins/Live/templates/index.tpl index 332d1ffabf..506973ade7 100644 --- a/plugins/Live/templates/index.tpl +++ b/plugins/Live/templates/index.tpl @@ -12,7 +12,7 @@ ajax: 'index.php?module=Live&idSite={/literal}{$idSite}{literal}&action=getLastVisitsStart', fadeLast: 2, isDupes : check_for_dupe, - timeout: 20000, + timeout: 8000, customParameterName: 'minIdVisit', customParameterValueCallback: lastIdVisit, fadeInSpeed: 1400 diff --git a/plugins/Live/templates/lastVisits.tpl b/plugins/Live/templates/lastVisits.tpl index 759617446f..22864037f7 100644 --- a/plugins/Live/templates/lastVisits.tpl +++ b/plugins/Live/templates/lastVisits.tpl @@ -14,7 +14,7 @@ </div> <!--<div class="settings"></div>--> <div class="referer"> - {if $visitor.refererType != 'directEntry'}from <a href="{$visitor.refererUrl}">{if !empty($visitor.searchEngineIcon)}<img src="{$visitor.searchEngineIcon}" /> {/if}{$visitor.refererName}</a> + {if $visitor.refererType != 'directEntry'}from <a href="{$visitor.refererUrl}" target="_blank">{if !empty($visitor.searchEngineIcon)}<img src="{$visitor.searchEngineIcon}" /> {/if}{$visitor.refererName}</a> {if !empty($visitor.keywords)}"{$visitor.keywords}"{/if} {/if} {if $visitor.refererType == 'directEntry'}Direct entry{/if} |