danieljharvey/stinky-query-finder

Find stinky queries

stinky-query-finder

Find what will probably turn out to be totally awful MySQL queries before they ruin everything. , _(*1)

What does it do then?, _(*2)

You pass it a PDO object and a SQL call, it learns a little about your DB, and works out whether your query is potentially garbage, _(*3)

How does it decide?, _(*4)

Basically, by whether a) the table is very long - if it's not, it'll be fine. b) if the table IS long, whether the query is using one or more indexes to give it a fighting chance of being OK., _(*5)

How do I use it?, _(*6)

Make a PDO object, pass it into a nice new Stinkers object, and ask it whether your SQL is dogshit or not., _(*7)

$dbName = "greatDB"

$dsn = "mysql:dbname={$dbName};host=127.0.0.1";

$pdo = new \PDO($dsn, "username", "excellentPassword");

$sql = "SELECT COUNT(1) FROM excellentTable WHERE thingID=2229 AND theDate='2017-05-12'";

$stinkers = new \DanielJHarvey\QueryStinkers\Stinkers($dbName, $tables, $pdo);

$problematicQuery = $stinkers->checkQuery($sql);

$problematicQuery will either return false (not a problem, great) or an array-based stack trace (so that the offending query creating code can be located), _(*8)

That's going to slow things down a bit isn't it?, _(*9)

Yeah, unfortunately so, so please please please don't use this in production. If you wish to speed things up, you can cache the DB tables data created by Stinkers like this:, _(*10)

$tables = $stinkers->getTables();

cacheTablesDataWithYourExcellentCachingSolution($tables);

Then you're free to use it again like this:, _(*11)

$dbName="excellentDB"
$tables = getCachedTablesFromYourGreatCachingFunction();

$stinkers = new \DanielJHarvey\QueryStinkers\Stinkers($dbName, $tables);

// no need to rebuild table data, everything is fine