Mysql Query Optimization for this particular query

Question

I am having a mysql query which is taking roughly 2.6s to execute. At first it was not using index. Now I added index to it. But still no improvements. Any suggestion on this:

select posummary0_.id                               as col_0_0_,
       popayment1_.status                           as col_1_0_,
       posummary0_.id                               as id1_11_,
       posummary0_.created_at                       as created_2_11_,
       posummary0_.created_by                       as created_3_11_,
       posummary0_.is_active                        as is_activ4_11_,
       posummary0_.updated_at                       as updated_5_11_,
       posummary0_.updated_by                       as updated_6_11_,
       posummary0_.uuid                             as uuid7_11_,
       posummary0_.additional_cost                  as addition8_11_,
       posummary0_.additional_cost_note             as addition9_11_,
       posummary0_.approved_at                      as approve10_11_,
       posummary0_.approved_by                      as approve11_11_,
       posummary0_.can_be_rejected                  as can_be_12_11_,
       posummary0_.close_reason                     as close_r13_11_,
       posummary0_.closed_at                        as closed_14_11_,
       posummary0_.closed_by                        as closed_15_11_,
       posummary0_.delivery_fee                     as deliver16_11_,
       posummary0_.eta_date                         as eta_dat17_11_,
       posummary0_.sku_farmer_id                    as sku_far35_11_,
       posummary0_.gr_price                         as gr_pric18_11_,
       posummary0_.invoice_date                     as invoice19_11_,
       posummary0_.is_vat                           as is_vat20_11_,
       posummary0_.payment_due_date                 as payment21_11_,
       posummary0_.payment_source                   as payment22_11_,
       posummary0_.po_discount                      as po_disc23_11_,
       posummary0_.po_number                        as po_numb24_11_,
       posummary0_.po_price                         as po_pric25_11_,
       posummary0_.po_status_id                     as po_stat36_11_,
       posummary0_.po_status_update_at              as po_stat26_11_,
       posummary0_.po_summary_payment_type_id       as po_summ37_11_,
       posummary0_.po_summary_type_id               as po_summ38_11_,
       posummary0_.po_vat_type_id                   as po_vat_39_11_,
       posummary0_.receipt_id_type                  as receipt27_11_,
       posummary0_.reject_reason                    as reject_28_11_,
       posummary0_.rejected_at                      as rejecte29_11_,
       posummary0_.rejected_by                      as rejecte30_11_,
       posummary0_.revised_at                       as revised31_11_,
       posummary0_.revised_by                       as revised32_11_,
       posummary0_.total_price                      as total_p33_11_,
       posummary0_.vat                              as vat34_11_,
       posummary0_.warehouse_id                     as warehou40_11_,
       posummary0_.warehouse_distribution_center_id as warehou41_11_,
       posummary0_.warehouse_kitchen_id             as warehou42_11_,
       posummary0_.warehouse_time_window_id         as warehou43_11_
from po_summaries posummary0_
         left outer join po_payments popayment1_ on (posummary0_.id = popayment1_.po_summary_id)
         cross join sku_farmers farmer2_
         cross join warehouses warehouse3_
         cross join po_status postatus4_
where posummary0_.sku_farmer_id = farmer2_.id
  and posummary0_.warehouse_id = warehouse3_.id
  and posummary0_.po_status_id = postatus4_.id
  and (1 is null or posummary0_.is_active = 1)
  and (null is null or posummary0_.created_at >= null)
  and (null is null or posummary0_.created_at <= null)
  and (null is null or posummary0_.eta_date >= null)
  and (null is null or posummary0_.eta_date <= null)
  and ('' is null or '' = '' or farmer2_.uuid = '')
  and ('' is null or '' = '' or warehouse3_.uuid = '')
  and ('%%' is null or '%%' = '' or postatus4_.code like '%')
  and ('%%' is null or '%%' = '' or posummary0_.po_number like '%')
  and (null is null or null = '' or posummary0_.created_by like null)
order by posummary0_.created_at desc
limit 160, 20;

the id field are already indexed. I additionally added index on this:

create index test_index_2 on po_status (code);

Before index explain output:

After index it became like this:

I am very new to mysql and need suggestion to improve performance.

One keynote: If I remove order by then the query execution time becomes so less.

Can anyone suggest me optimization for this query? Plan:

Answer 1

(I think that is the worst programmatically generated SQL that I have ever seen. What program created it? Still, it might actually work as it stands!)

This one composite index may be a partial salvation:

posummary0_:  INDEX(is_active, created_at)

and when may have to change

and  (1 is null
              or  posummary0_.is_active = 1
           )

to

and  posummary0_.is_active = 1

(OR is deadly for performance. This OR comes from a lazy query-generator.)

If you feed it different options, you may need another INDEX.

After cleaning up the query, we can talk about turning it inside-out. This will allow the ORDER BY, OFFSET, and LIMIT to occur before all the JOINs. Currently the query is doing 574144 joins. With the reformulation, it will need only 104.

Inside out

First, see if this provides the desired 20 ids and runs 'fast':

select  posummary0_.id
    posummary0_.is_active = 1
    order by  posummary0_.created_at desc
    limit  160, 20;

Then build a query something like

SELECT ...  -- mostly as above
    FROM ( the 4 lines above ) AS x
    JOIN ...   -- the rest of the SQL, but without the LIMIT