How to improve query

Question

I have 3 tables

A1: name, id
A2: a_id, b_id (can be more than one a_id => b_id)
B1: id, *

I use subquery with leftJoin on A2 with A1 but operation is very slow (it fetch 10s) In B1 I fetch data and I need to paginate B1 by A2 unique instances and filter by fields Without this query B1 fetches less than 0.5s.

I found solution to set OPTION (FORCE ORDER) but it cause error in laravel query;

I'm using

     $query = $query->toSql();
      $data = DB::select(DB::raw($query . " OPTION (FORCE ORDER)"));
      // fetch data

      $this->startCondition()
        ->leftJoinSub($data, 'query_data', function ($join) {
          $join->on('query_data.lead_id', '=', 'table.id');
      });

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION\t(FORCE ORDER)' at line 1

I have improved query adding the index by two columns (a_id, b_id, created_at) but it's not enought. It gets 25% more query speed, now i lost 7.5 seconds to fetch 25 rows from table

create index A2_index_o on A2(a_id, b_id, created_at);

Please share more details, like the generated query and you attempts to resolve the problem — Nico Haase, May 26 '23 at 12:27
https://stackoverflow.com/questions/3456134/is-there-a-way-to-force-mysql-execution-order — Your Common Sense, May 26 '23 at 12:36

score 0 · Accepted Answer · answered May 29 '23 at 14:31

I have fetched first simple pagination for current page like a1=>a2, get the ids for current offset and put it to subquery;

 $this->prepared = array_map(fn($item) => $item->id, $prepare->items());


 $subquery->whereIn('lead_id', $this->prepared)

This saved for me 5 more seconds. Also i added optional filtering if order was used for sorting, but it dont get speed improvement.

Main part of my problem was solved when query was run faster.

For db query now looks like

    A1 data | null | null
 * current offset *
    A1 data | A2 data | B1 data
 * end of current offset *
    A1 data | null | null

How to improve query

1 Answers1