zortje/mysql-keeper

Find incorrect configurations or missed optimization opportunities in MySQL databases

0.4.0 2015-02-22 00:33 UTC

Requires

• php: >=5.4
• ext-pdo: ~1.0
• ext-pdo_mysql: ~1.0

Requires (Dev)

• codeclimate/php-test-reporter: dev-master
• phpunit/phpunit: ~4.0

MIT

• René Tønner Stokholm

• mysql
• optimization

README

Find incorrect configurations or missed optimization opportunities in MySQL databases.

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

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 = DatabaseFactory::create($pdo);
$databaseResult = $database->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'
			]
		]
	]
];

Usage with Melody

Melody allows inline Composer requirements to simply execute PHP scripts.

mysql-keeper_myapp.php

<?php

<<<CONFIG
packages:
    - "zortje/mysql-keeper": "~0.0"
CONFIG;

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

$database = Zortje\MySQLKeeper\DatabaseFactory::create($pdo);

print_r($database->getResult());

Simply run:

$ melody run mysql-keeper_myapp.php