スキーマの最適化
Before
Schema::create('users', function (BluePrint $table) {
$table->bigIncrements();
$table->string('screen_name')->index(); // ←論理削除があるので ->unique() にはできない
$table->softDeletes();
});
After
MySQL5.7から使える生成カラムと，ユニーク制約がNULLには適用されないことを利用します。アプリケーション側のコードには，一切手を加える必要がありません。
Schema::create('users', function (BluePrint $table) {
$table->bigIncrements();
$table->string('screen_name');
$table->softDeletes();
// 論理削除されていれば NULL， されていなければ 1 になる生成カラムを定義
$table->boolean('existence')->nullable()->storedAs('CASE WHEN deleted_at IS NULL THEN 1 ELSE NULL END');
// screen_name と existence に対して複合ユニークインデックスを張る
// （カーディナリティの低い existence を後ろに持ってくるべき）
$table->unique(['screen_name', 'existence']);
});
カラムの命名に迷いますが， exists だと 1 か 0 を取りそうな雰囲気があるので，存在そのものを表す名詞形にして 1 か NULL っぽさを出すようにしてみました。
SELECTするSQLの最適化
Before
User::whereScreenName('mpyw')->first()
これだと
WHERE screen_name = 'mpyw' AND deleted_at IS NOT NULL
という条件がつくので，せっかく貼ったユニークインデックスがフルに利用されません。
After
User::whereScreenName('mpyw')->withTrashed()->whereExistence(true);
こっちなら
WHERE screen_name = 'mpyw' AND existence = 1
となり，理論上は最速のクエリになります。