Combine multiple SQL queries into single query for speed

Question

I am working in PHP with Laravel and the page I have been given contains 3 SQL queries on separate tables but using the same criteria. I have tried combining them but the combined results are slower than the original. What can I do to increase rather than decrease the speed of these queries?

Here are my queries:

        $searchFor = 'debtor_name';
        $searchForBKR = 'estate_name';
        $searchForHypotech = 'hypleg_lot_credit_debtor_name';

        $arraySearch = explode(",", $request->input('name'));
        $search = $searchFor . ' like "%';
        $bkrSearch = $searchForBKR . ' like "%';
        $hypotechSearch = $searchForHypotech . ' like "%';
        $searchCompany =  $searchCompanyFor . ' like "%';
        $searchPlaintiff = $searchPlaintiffFor . ' like "%';
        $searchDefendantName = $searchDefendantNameFor . ' like "%';

        $search = $search . $arraySearch[0] . '%"';
        $bkrSearch = $bkrSearch . $arraySearch[0] . '%"';
        $hypotechSearch = $hypotechSearch . $arraySearch[0] . '%"';
        $searchCompany = $searchCompany . $arraySearch[0] . '%"';
        $searchPlaintiff = $searchPlaintiff . $arraySearch[0] . '%"';
        $searchDefendantName = $searchDefendantName . $arraySearch[0] . '%"';

        for ($i = 1; $i < count($arraySearch); $i++) {
            $search = $search . " or " . $searchFor . ' like "%'   . $arraySearch[$i] . '%"';
            $bkrSearch = $bkrSearch . " or " . $searchForBKR . ' like "%'   . $arraySearch[$i] . '%"';
            $hypotechSearch = $hypotechSearch . " or " . $searchForHypotech . ' like "%'   . $arraySearch[$i] . '%"';
            $searchCompany = $searchCompany . " or " . $searchCompanyFor . ' like "%'   . $arraySearch[$i] . '%"';
            $searchPlaintiff = $searchPlaintiff . " or " . $searchPlaintiffFor . ' like "%'   . $arraySearch[$i] . '%"';
            $searchDefendantName = $searchDefendantName . " or " . $searchDefendantNameFor . ' like "%'   . $arraySearch[$i] . '%"';
        }



$sis = DB::select('select debtor_name, count(*) as sis_total from equifax_sis_regions' . $search . ')
                            group by debtor_name');

            $bkr = DB::select('select estate_name as debtor_name, count(*) as bkr_total from equifax_bkr_regions' . $bkrSearch . ')
                            group by estate_name');

            $hypotech = DB::select('select hypleg_lot_credit_debtor_name as debtor_name, count(*) as hypotech_total from equifax_hypotech_regions' . $hypotechSearch . ')
                            group by hypleg_lot_credit_debtor_name');

I have tried replacing the 3 queries by using outer joins like this:

$other = 'select debtor_name, count(debtor_name) as sis_total, 
                    estate_name as debtor_name, count(estate_name) as bkr_total 
                    hypleg_lot_credit_debtor_name as debtor_name, count(hypleg_lot_credit_debtor_name) as hypotech_total
                    from equifax_sis_regions outer join equifax_bkr_regions on (equifax_sis_regions.debtor_name=estate_name)
                    outer join equifax_hypotech_regions on (equifax_sis_regions.debtor_name=hypleg_lot_credit_debtor_name)
                    WHERE (' . $search . ') OR ' . $bkrSearch . ') OR (' . $hypotechSearch . ')
                    GROUP BY debtor_name, estate_name, hypleg_lot_credit_debtor_name';

but that is a much slower query to run. The database contains over 4 million rows and the difference in API calls to the 2 query versions is ~53 seconds for the original and ~78 seconds for the combined form. Is there any way I can modify this combined query to be faster rather than slower?

Thanks in advance

user_5413565 · Answer

You can use the UNION ALL operator to combine multiple SELECT statements. You could combine them like this:

SELECT debtor_name, SUM(sis_total), SUM(bkr_total), SUM(hypotech_total)
FROM (
         SELECT debtor_name, COUNT(*) AS sis_total, 0 AS bkr_total, 0 AS hypotech_total
         FROM equifax_sis_regions
         -- add where clause here
         GROUP BY debtor_name

         UNION ALL

         SELECT estate_name AS debtor_name, 0 AS sis_total, COUNT(*) AS bkr_total, 0 AS hypotech_total
         FROM equifax_bkr_regions
         -- add where clause here
         GROUP BY estate_name

         UNION ALL

         SELECT hypleg_lot_credit_debtor_name AS debtor_name, 0 AS sis_total, 0 AS bkr_total, COUNT(*) AS hypotech_total
         FROM equifax_hypotech_regions
         -- add where clause here
         GROUP BY hypleg_lot_credit_debtor_name
     ) derived
GROUP BY debtor_name;

Combine multiple SQL queries into single query for speed

1 Answers