A node.js script to help find missing indexes, FK, PK and mismatched ENGINE types (prefers INNODB).

mysql helper.js

// This script will connect to the DB and look for potential missing indexes.
// Author: Liam Mitchell
var db = 'database here';

var async = require('async');

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'host here',
  user     : 'username here',
  password : 'password here',
  database : db
});

// Connect to the database.
connection.connect();


// Get the tables.
connection.query('SHOW TABLES', function (error, results, fields) {
  if (error) throw error;

  var tables = [];
  for (var i=0; i< results.length; i++) {
  	var table = { name: results[i]['Tables_in_'+db], indexes: [], columns: [], suggestedChanges: [], fks: [] };
  	tables.push(table);
  }

  console.log('Read table names from '+db+' ('+tables.length+' tables).');

  getIndexesOnTables(tables);
});


function getIndexesOnTables(tables) {
	async.eachSeries(tables, function iteratee(table, callback) {
		connection.query('SHOW INDEXES FROM '+table.name, function (error, results, fields) {
			if (error) {
				callback(error);
				return;
			}

			if (results.length > 0) {
				console.log('Table '+table.name+' has had '+results.length+' index(s) read.');
				table.indexes = results;
			} else {
				console.log('Table '+table.name+' has no indexes.');
			}

			async.parallel([
				getColumnsForTable.bind(null, table),
				getFKForTable.bind(null, table),
				getEngineInfoForTable.bind(null, table)
			], function(err, results) {
			    callback(err, results);
			});
		});
	}, function(err, results){
		if (err) {
			console.error(err);
			return;
		} 
		console.log('All tables have had their indexes looked up.');
		var tableNameLookup = {};
		for (var i=0; i<tables.length;i++) {
			tableNameLookup[tables[i].name] = tables[i];
		}

		// For each table check for missing indexes and missing PK or FK.
		for (var i=0; i<tables.length;i++) {
			var table = tables[i];
			var keyCount = 0;
			var hasPrimaryKey = false;
			for (var j=0; j<table.columns.length; j++) {
				var column = table.columns[j];
				var columnName = column.Field;

				if (column.Key !== '') {
					keyCount++;
				
					if (column.Key === 'PRI') {
						hasPrimaryKey = true;
					}
				}

				if (j===0 && hasPrimaryKey === false) {
					console.log('Table '+table.name+' does not have a primary key as first column '+columnName);
				}

				if (columnName === 'id' || (columnName.endsWith('id') || columnName.endsWith('Id')) && !columnName.endsWith('paid') ) {
					var indexAlreadyExistsOnThatKey = false;
					for (var k=0; k<table.indexes.length; k++) {
						var index = table.indexes[k];
						if (index.Key_name === columnName) {
							indexAlreadyExistsOnThatKey = true;
							break;
						}
					}

					if (indexAlreadyExistsOnThatKey === false) {
						console.log('Index not found for '+table.name+'.'+columnName+' suggesting to add one.');
						table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD INDEX ('+columnName+');');
					} else {
						console.log('Index found for '+table.name+'.'+columnName+' skipping.');
					}

					var fkExistsAlready = false;
					for (var k=0; k<table.fks.length; k++) {
						var fk = table.fks[k];
						if (fk.column_name === columnName) {
							console.log('FK Already exists for '+table.name+'.'+columnName+ ' on ' +fk.referenced_table_name+'.'+fk.referenced_column_name);
							fkExistsAlready = true;
							break;
						}
					}

					if (fkExistsAlready === false) {
						var relationTableGuess = columnName.substr(0, columnName.length-2).trim();
						if (relationTableGuess.length > 2) {
							console.log('Trying to find relationship of '+table.name+'.'+columnName);
							if (relationTableGuess.endsWith('_')) {
								relationTableGuess = relationTableGuess.substr(0, relationTableGuess.length-1);
							}

							var otherTable = tableNameLookup[relationTableGuess];
							if (otherTable === undefined) {
								otherTable = tableNameLookup[relationTableGuess+'s'];
							}

							if (otherTable) {
								console.log('Other table '+otherTable.name+' was found.');

								if (otherTable.columns.length > 0) {
									var isOtherTableFirstColumnPK = false;
									if (otherTable.columns[0].Key === 'PRI') {
										isOtherTableFirstColumnPK = true;
										console.log('First column in otherTable is '+otherTable.columns[0].Field+' and it is a PRIMARY KEY.');
									} else {
										console.log('First column in otherTable is '+otherTable.columns[0].Field+' and it is not a PRIMARY KEY.');
									}

									if (table.engine !== otherTable.engine) {
										console.warn('Tables '+table.name+' ('+table.engine+') and '+otherTable.name+' ('+otherTable.engine+') have different engines so a FK between them may not work.');
										if (table.engine === 'MyISAM') {
											table.suggestedChanges.push('ALTER TABLE `'+table.name+'` ENGINE=INNODB;');
										}
										if (otherTable.engine === 'MyISAM') {
											otherTable.suggestedChanges.push('ALTER TABLE `'+otherTable.name+'` ENGINE=INNODB;');
										}
									}

									// TODO: Check if there are any records that violate this constraint and create an appropriate commented out DELETE query or at least warn.
									table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD CONSTRAINT fk_'+table.name+'_'+columnName+' FOREIGN KEY ('+columnName+') REFERENCES '+otherTable.name+'('+otherTable.columns[0].Field+');');
								} else {
								  console.log('No table found for the relationship of '+table.name+'.'+columnName);
							        }

							}
						}
					}

				}
			}

			table.hasPrimaryKey = hasPrimaryKey;
			table.keyCount = keyCount;

			if (table.keyCount === 0) {
				console.log('Table '+table.name+' has no keys!');
			} else if (hasPrimaryKey === false) {
				console.log('Table '+table.name+' has no PK suggesting to add first key as primary key!');
				table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD PRIMARY KEY('+table.columns[0].Field+');');
			}
		}

		console.log('-- Suggested changes to DB schema.');
		console.log();
		for (var i=0; i<tables.length;i++) {
			var table = tables[i];
			if (table.suggestedChanges.length > 0) {
				table.suggestedChanges = unique(table.suggestedChanges);
				console.log('-------------------------------------------');
				console.log('-- Table '+table.name+' suggested changes.');
				console.log(table.suggestedChanges.join("\n"));
				console.log();
			}
		}

		exit();
	});
}

function getColumnsForTable(table, callback) {
	connection.query('SHOW COLUMNS FROM '+table.name, function (error, results, fields) {
		if (error) {
			callback(error);
			return;
		}

		table.columns = results;

		callback(null);
	});
	
}

function getFKForTable(table, callback) {
	connection.query("SELECT column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL AND table_schema = '"+db+"' AND table_name = '"+table.name+"'", function (error, results, fields) {
		if (error) {
			callback(error);
			return;
		}

		table.fks = results;

		callback(null);
	});
	
}

function getEngineInfoForTable(table, callback) {
	connection.query("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+db+"' AND TABLE_NAME = '"+table.name+"'", function (error, results, fields) {
		if (error) {
			callback(error);
			return;
		}

		if (results.length > 0) {
			table.engine = results[0].ENGINE;
		}

		callback(null);
	});
	
}



// Exit
function exit() {
	console.log('-- Generated by a script on '+(new Date()));
	connection.end();
}


/// Polyfills

// Ends with
if (!String.prototype.endsWith) {
  String.prototype.endsWith = function(searchString, position) {
      var subjectString = this.toString();
      if (typeof position !== 'number' || !isFinite(position) || Math.floor(position) !== position || position > subjectString.length) {
        position = subjectString.length;
      }
      position -= searchString.length;
      var lastIndex = subjectString.lastIndexOf(searchString, position);
      return lastIndex !== -1 && lastIndex === position;
  };
}

function unique(arr) {
    var hash = {}, result = [];
    for ( var i = 0, l = arr.length; i < l; ++i ) {
        if ( !hash.hasOwnProperty(arr[i]) ) { //it works with objects! in FF, at least
            hash[ arr[i] ] = true;
            result.push(arr[i]);
        }
    }
    return result;
}