darxmac/mysql-keeper

Find incorrect configurations or missed optimization opportunities in MySQL databases

MySQL Keeper

Find incorrect configurations or missed optimization opportunities in MySQL databases.

Installing via Composer

The recommended way to install MySQL Keeper is though Composer.

{
    "require": {
        "zortje/mysql-keeper": "~0.0"
    }
}

Usage

$pdo = new PDO('mysql:host=127.0.0.1;dbname=myapp', 'root', '');

$database = new DatabaseFactory::create($pdo);
$databaseResult = $database->getResult();

$table = new TableFactory::create('users', $pdo);
$tableResult = $table->getResult();

The $databaseResult array will look something like this with the first level keys being table names.

[
	'users' => [
		'issues' => [
			[
				'type' => 'field',
				'field' => 'id',
				'description' => 'Set as auto_increment but has no primary key'
			],
			[
				'type' => 'field',
				'field' => 'id',
				'description' => 'Set as auto_increment but is not set as primary'
			]
		],
		'optimizations' => [
			[
				'type' => 'field',
				'field' => 'id',
				'description' => 'Field should be unsigned, as no field values are negative'
			]
		]
	]
];

The $tableResult array will look something like this.

[
	'issues' => [
		[
			'type' => 'field',
			'field' => 'id',
			'description' => 'Set as auto_increment but is not set as primary'
		],
		[
			'type' => 'field',
			'field' => 'id',
			'description' => 'Set as auto_increment but is not set as primary'
		]
	],
	'optimizations' => [
		[
			'type' => 'field',
			'field' => 'id',
			'description' => 'Field should be unsigned, as no field values are negative'
		]
	]
];

Coming Soon™, how to easily run MySQL Keeper with Melody

Features

Detects the following issues

• Duplicate indices
• Missing primary key on auto_increment column
• Redundant unique index on primary key
• Redundant key index on primary key
• Inconsistent usage of collation between table and columns