بهره وری دیتابیس پروژه لاراولی- زمانی که بهتر است کوئری های بیشتری اجرا کنیم.

در قسمت قبل دیدیم که استفاده از whereIn می تواند بسیار سریع تر از   whereHas و حتا join باشد. استفاده از whereIn باعث شد کوئری ما از ایندکس موجود در جدول companies استفاده کند و سرعت اجرای آن بیشتر شود. اما هنوز هم موفق نشدیم از ایندکس های  جدول users استفاده کنیم. 

در این قسمت یک راه حل جالب برای این مشکل پیدا خواهیم کرد. می خواهیم از قصد چندین کوئری اجرا کنیم. با انجام این کار می توانیم هر کوئری را به صورت ساده و ایزوله بنویسیم تا در بهینه ترین حالت ممکن اجرا شوند.

با کپی کردن کوئری و آزمون و خطا در phpMyAdmin شروع می کنیم.

در قسمت قبل به این کوئری رسیدیم.

SELECT
    *
FROM
    `users`
WHERE
   (
        `first_name` LIKE 'bill%' OR `last_name` LIKE 'bill%' OR `company_id` IN(
        SELECT
            `id`
        FROM
            `companies`
        WHERE
            `name` LIKE 'bill%'
    )
   ) AND(
        `first_name` LIKE 'gates%' OR `last_name` LIKE 'gates%' OR `company_id` IN(
        SELECT
            `id`
        FROM
            `companies`
        WHERE
            `name` LIKE 'gates%'
  )
    ) AND(
        `first_name` LIKE 'microsoft corp%' OR `last_name` LIKE 'microsoft corp%' OR `company_id` IN(
        SELECT
            `id`
        FROM
            `companies`
        WHERE
            `name` LIKE 'microsoft corp%'
   )
   )
LIMIT .15 OFFSET 0

اولین چیزی که امتحان می کنیم، اجرای sub query شرکت ها به صورت جداگانه است.

این کوئری بسیار سریع است و در کمتر از یک میلی ثانیه اجرا می شود. همچنین اگر explain آن را مشاهده کنیم می بینیم که از ایندکس جدول companies استفاده می کند.

بنابراین، این کوئری به خودی خود بهینه عمل می کند. بیاید sub query موجود در کوئری اصلی را با مقداری که از اجرای جداگانه آن بدست آوردیم جایگزین کنیم. از آن جایی که می دانیم هیچ شرکتی با عبارت های bill و gates هم پیدا نمی شوند به طور کلی آن ها را حذف می کنیم. در نهایت کوئری ما مانند زیر می شود.

SELECT
    *
FROM
    `users`
WHERE
    (
        `first_name` LIKE 'bill%' OR `last_name` LIKE 'bill%'
    ) AND(
        `first_name` LIKE 'gates%' OR `last_name` LIKE 'gates%'
    ) AND(
        `first_name` LIKE 'microsoft corp%' OR `last_name` LIKE 'microsoft corp%' OR `company_id` IN(1000)
    )
LIMIT 15 OFFSET 0

این کوئری را اجرا می کنیم.

در کمتر از یک میلی ثانیه اجرا شد! اکنون Explain آن را مشاهده می کنیم.

کوئری ما اکنون از ایندکس های جدول users استفاده می کند. هر دوی این کوئری ها به تنهایی در کمتر از یک میلی ثانیه اجرا شدند. اما درحالتی که باهم ترکیب شده بودند، اجرای آن ها بیشتر از 10 میلی ثانیه طول می کشید. این تفاوت بزرگی است. دلیل این اتفاق این است که کوئری ها به صورت ایزوله اجرا می شوند و می توانند از ایندکس های موجود استفاده کنند.

همین تغییر را در کد های برنامه هم اعمال می کنیم.

public function scopeSearch($query, string $terms = null)
{
    collect(str_getcsv($terms, ' ', '"'))->filter()->each(function ($term) use ($query) {
        $term = $term . '%';
        $query->where(function ($query) use ($term) {
            $query->where('first_name', 'like', $term)
                ->orWhere('last_name', 'like', $term)
                ->orWhereIn('company_id', Company::query()
                    ->where('name', 'like', $term)
                    ->pluck('id')
                );
        });
    });
}

اکنون به مرورگر بر می گردیم و دوباره جست و جو را انجام می دهیم.

زمان اجرای کوئری ها از حدود 150 میلی ثانیه به 15 میلی ثانیه رسید! فوق العاده است.

همانطور که می بینید اکنون سه کوئری جدید داریم (برای هر عبارت جست و جو شده یک کوئری)  اما زمان اجرای آن ها بسیار سریع است. این روش برای من جالب است زیرا ما همیشه در تلاشیم تا کوئری های کمتری اجرا کنیم. با این که اجرای هر کوئری باری به برنامه اضافه می کند، در چنین شرایطی که اجرای کوئری های جدا موجب استفاده از ایندکس ها می شود بهتر است که کوئری های بیشتری اجرا کنیم. با تمام این توضیحات من فکر می کنم همچنان راهی برای اجرای یک کوئری به جای سه کوئری وجود دارد. این راه را در قسمت بعدی مشاهده خواهیم کرد.