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

Mushif Ali Nawaz · Accepted Answer · 2021-04-20 13:18:04Z

2

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;

answered Apr 20, 2021 at 13:18

Mushif Ali Nawaz

3,9163 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Geoff Over a year ago

That worked amazingly well. Thank you. It cut my response time from 78 seconds to approximately 11 seconds. Again thanks for the information. I have never used UNION ALL, or even considered it. If I may ask, though, what does the 'derived' do?

Mushif Ali Nawaz Over a year ago

@Geoff I'm glad that my answer helped you. This 'derived' is an alias for the derived table that is created by the subquery in parentheses. Then this 'derived' table is queried to get the final result.

Geoff Over a year ago

Thanks again. I didn't see it referenced in the query so was wondering if it was a command. The explanation is helpful

Mushif Ali Nawaz Over a year ago

@Geoff as it's inside the FROM clause, we don't need to reference it unless explicitly required. We can also use derived.debtor_name etc

Collectives™ on Stack Overflow

Combine multiple SQL queries into single query for speed

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related