function suggest_index($table_alias) { global $DB; $suggest_table = null; /** @var CPerfQueryTable $table */ foreach ($this->from->tables as $table) { if ($this->cmp($table->alias, $table_alias)) { $suggest_table = $table; } } if (!isset($suggest_table)) { return false; } $arTableJoins = $this->table_joins($table_alias); //Next read indexes already have $arSuggest = array(); if (!empty($arTableJoins)) { if (!$DB->TableExists($suggest_table->name)) { return false; } $table = new CPerfomanceTable(); $arIndexes = $table->GetIndexes($suggest_table->name); foreach ($arIndexes as $index_name => $arColumns) { $arIndexes[$index_name] = implode(",", $arColumns); } //Test our suggestion against existing indexes foreach ($arTableJoins as $i => $arColumns) { $index_found = ""; $arColumns = $this->_adjust_columns($arColumns); //Take all possible combinations of columns $arCombosToTest = $this->array_power_set($arColumns); foreach ($arCombosToTest as $arComboColumns) { if (!empty($arComboColumns)) { $index2test = implode(",", $arComboColumns); //Try to find out if index already exists foreach ($arIndexes as $index_name => $index_columns) { if (substr($index_columns, 0, strlen($index2test)) === $index2test) { if ($index_found === "" || count(explode(",", $index_found)) < count(explode(",", $index2test))) { $index_found = $index2test; } } } } } // if (!$index_found) { sort($arColumns); $arSuggest[] = $suggest_table->alias . ":" . $suggest_table->name . ":" . implode(",", $arColumns); } } } if (!empty($arSuggest)) { return $arSuggest; } else { return false; } }
public function suggest_index($table_alias) { global $DB; $suggest_table = null; foreach($this->from->tables as $table) if($table->alias === $table_alias) $suggest_table = $table; if(!isset($suggest_table)) return false; $arTableJoins = $this->table_joins($table_alias); //Next read indexes already have //echo "<pre>",htmlspecialcharsbx(print_r($arTableJoins,1)),"</pre>"; $arSuggest = array(); if(!empty($arTableJoins)) { if(!$DB->TableExists($suggest_table->name)) return false; $arIndexes = CPerfomanceTable::GetIndexes($suggest_table->name); foreach($arIndexes as $index_name => $arColumns) $arIndexes[$index_name] = implode(",", $arColumns); //echo "<pre>",htmlspecialcharsbx(print_r($arIndexes,1)),"</pre>"; //Test our suggestion against existing indexes foreach($arTableJoins as $i => $arColumns) { $index_found = ""; $arColumns = $this->_adjust_columns($arColumns); //Take all possible combinations of columns $arCombosToTest = $this->array_power_set($arColumns); //echo "<pre>",htmlspecialcharsbx(print_r($arCombosToTest,1)),"</pre>"; foreach($arCombosToTest as $arComboColumns) { if(!empty($arComboColumns)) { $index2test = implode(",", $arComboColumns); //Try to find out if index alredy exists foreach($arIndexes as $index_name => $index_columns) { if(substr($index_columns, 0, strlen($index2test)) === $index2test) { if( $index_found === "" || count(explode(",", $index_found)) < count(explode(",", $index2test)) ) $index_found = $index2test; } } } } // if(!$index_found) { sort($arColumns); $arSuggest[] = $suggest_table->alias.":".$suggest_table->name.":".implode(",", $arColumns); } } } if(!empty($arSuggest)) { return $arSuggest; } else { return false; } }
// Id of the edited record $rsSuggest = CPerfomanceIndexSuggest::GetList(array("ID", "TABLE_NAME", "TABLE_ALIAS", "COLUMN_NAMES", "SQL_TEXT", "SQL_EXPLAIN", "SQL_TIME", "SQL_COUNT"), array("=ID" => $ID), array()); $arSuggest = $rsSuggest->Fetch(); if (!$arSuggest) { $APPLICATION->AuthForm(GetMessage("ACCESS_DENIED")); } $sql = CPerfomanceSQL::Format($arSuggest["SQL_TEXT"]); $sql = htmlspecialcharsEx($sql); $sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql); foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) { $sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql); } $arSuggest["FORMATTED_SQL_TEXT"] = $sql; $arColumns = explode(",", $arSuggest["COLUMN_NAMES"]); $arTableStat = CPerfQueryStat::GatherTableStat($arSuggest["TABLE_NAME"]); $arIndexes = CPerfomanceTable::GetIndexes($arSuggest["TABLE_NAME"]); $arQueries = array(); $rsQueries = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT"), array("=SUGGEST_ID" => $ID), array("ID" => "ASC"), false, array("nTopCount" => 10)); while ($arQuery = $rsQueries->Fetch()) { $arQuery["STAT"] = array(); $arQuery["WHERE"] = array(); $arQuery["JOIN"] = array(); $q = new CPerfQuery(); if ($q->parse($q->transform2select($arQuery["SQL_TEXT"]))) { foreach ($arColumns as $column_name) { $arQuery["WHERE"][$column_name] = $q->find_value($arSuggest["TABLE_NAME"], $column_name); if ($arQuery["WHERE"][$column_name] == "") { $arQuery["JOIN"][$column_name] = $q->find_join($arSuggest["TABLE_NAME"], $column_name); } else { $arQuery["JOIN"][$column_name] = ""; }
$rsSuggest = CPerfomanceIndexSuggest::GetList(array("ID", "TABLE_NAME", "TABLE_ALIAS", "COLUMN_NAMES", "SQL_TEXT", "SQL_EXPLAIN", "SQL_TIME", "SQL_COUNT"), array("=ID" => $ID), array()); $arSuggest = $rsSuggest->Fetch(); if (!$arSuggest) { $APPLICATION->AuthForm(GetMessage("ACCESS_DENIED")); } $sql = CPerfomanceSQL::Format($arSuggest["SQL_TEXT"]); $sql = htmlspecialcharsEx($sql); $sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql); foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) { $sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql); } $arSuggest["FORMATTED_SQL_TEXT"] = $sql; $arColumns = explode(",", $arSuggest["COLUMN_NAMES"]); $arTableStat = CPerfQueryStat::GatherTableStat($arSuggest["TABLE_NAME"]); $obTable = new CPerfomanceTable(); $arIndexes = $obTable->GetIndexes($arSuggest["TABLE_NAME"]); $arQueries = array(); $rsQueries = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT"), array("=SUGGEST_ID" => $ID), array("ID" => "ASC"), false, array("nTopCount" => 10)); while ($arQuery = $rsQueries->Fetch()) { $arQuery["STAT"] = array(); $arQuery["WHERE"] = array(); $arQuery["JOIN"] = array(); $q = new CPerfQuery(); if ($q->parse($q->transform2select($arQuery["SQL_TEXT"]))) { foreach ($arColumns as $column_name) { $arQuery["WHERE"][$column_name] = $q->find_value($arSuggest["TABLE_NAME"], $column_name); if ($arQuery["WHERE"][$column_name] == "") { $arQuery["JOIN"][$column_name] = $q->find_join($arSuggest["TABLE_NAME"], $column_name); } else { $arQuery["JOIN"][$column_name] = ""; }