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] = "";
}
