Index Recommendation Reporter

Code

<?php

/*
 * Copyright (c) 2026 Jeffery L. Paris <jparis@phpog.com>.
 * Free for personal and internal use. Paid project use requires visible credit
 * to Jeffery L. Paris. Corporate use requires a paid license fee unless a
 * separate written license states otherwise.
 */

/**
 * Builds conservative index recommendations from observed filter and sort usage.
 *
 * The output is advisory only. It does not execute schema-changing statements.
 *
 * @param array $usage Query usage rows keyed by table or as table/columns rows.
 * @param array $schema Existing schema/index metadata.
 * @param array $options Optional minimum usage threshold.
 * @return array Index recommendation report.
 */
function ogRecommendIndexes($usage = array(), $schema = array(), $options = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	if (!is_array($usage)) {
		$result['message'] = 'Usage data must be an array.';
		return $result;
	}

	if (!is_array($schema)) {
		$schema = array();
	}
	if (!is_array($options)) {
		$options = array();
	}

	$minimum_uses = 2;
	if (!empty($options['minimum_uses'])) {
		$minimum_uses = (int)$options['minimum_uses'];
	}
	if ($minimum_uses < 1) {
		$minimum_uses = 1;
	}

	$existing = array();
	if (!empty($schema['indexes']) && is_array($schema['indexes'])) {
		foreach ($schema['indexes'] as $index) {
			if (!is_array($index) || empty($index['table']) || empty($index['columns'])) {
				continue;
			}
			$existing_key = $index['table'] . ':' . implode(',', (array)$index['columns']);
			$existing[$existing_key] = true;
		}
	}

	$recommendations = array();
	foreach ($usage as $row) {
		if (!is_array($row) || empty($row['table']) || empty($row['columns'])) {
			continue;
		}
		$table = trim((string)$row['table']);
		if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
			continue;
		}

		$columns = array();
		foreach ((array)$row['columns'] as $column) {
			$column = trim((string)$column);
			if (preg_match('/^[a-zA-Z0-9_]+$/', $column)) {
				$columns[] = $column;
			}
		}
		if (empty($columns)) {
			continue;
		}

		$uses = 1;
		if (!empty($row['uses'])) {
			$uses = (int)$row['uses'];
		}
		if ($uses < $minimum_uses) {
			continue;
		}

		$key = $table . ':' . implode(',', $columns);
		if (!empty($existing[$key])) {
			continue;
		}

		$index_name = 'idx_' . strtolower($table . '_' . implode('_', $columns));
		$recommendations[] = array(
			'table' => $table,
			'columns' => $columns,
			'uses' => $uses,
			'index_name' => substr($index_name, 0, 64),
			'reason' => 'Repeated filter or sort usage without a matching known index.'
		);
	}

	$result['success'] = true;
	$result['message'] = 'Index recommendations prepared.';
	$result['data'] = array('recommendations' => $recommendations);

	return $result;
}