We recently discovered that one of our foreign keys was of type varchar instead of bigInt. It was a huge bottleneck and was consuming something like 75% of our RDS and had been in the codebase for 3 years...
I wondered if there was any kind of command I could run against our schema to check if other columns have the wrong type assigned? Something like:
// pseudo AF but:
select columns.* from our_schema where column.name LIKE '_id' and column.type = 'varChar'
I was able to achieve this within my Laravel environment like so:
$schema = collect(DB::connection()->getDoctrineSchemaManager()->listTableNames())->map(function ($item, $key) {
return [
'name' => $item,
'columns' => DB::getSchemaBuilder()->getColumnListing($item)
];
});
foreach ($schema as $table) {
foreach ($table['columns'] as $columnName) {
if (!strpos($columnName, '_id')) {
continue;
}
try {
$columnType = DB::getSchemaBuilder()->getColumnType($table['name'], $columnName);
if ($columnType !== 'bigint') {
logger(["column name" => $columnName, "table name" => $table['name'], "column type" => $columnType]);
}
} catch (\Exception $e) {
// do nothing
}
}
}