It is my understanding from
https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html the predicate in an outer query should be used in the view query. Either I'm misreading this or the solution escapes me.
My expectation is that the predicate
where line_item_auto_key = 1112121
would be pushed down to the view.
for this query:
explain select * from order_item_oi_only where line_item_auto_key = 1112121
and getting:
```
1 PRIMARY <derived2> ALL 143159 100.00
2 DERIVED i ALL 143159 100.00 Using temporary; Using filesort
2 DERIVED o eq_ref PRIMARY,PARTNER_ACCOUNT_ID_IDX,ACCOUNT_ID_IDX,USER_ID_IDX,ORDER_ENTRY_TIME_IDX,CUST_REF_NUM_IDX,USER_COMMENT_IDX PRIMARY 8 wtg_ecomm_dev.i.ORDER_ID 1 100.00
```
Query is executing the following view:
CREATE OR REPLACE VIEW `Order_Item_oi_only` AS
SELECT i.line_item_auto_key, JSON_OBJECT(
'accountId', o.ACCOUNT_ID,
'buildDate', o.ORDER_BILLED,
'billingType', o.BILLING_TYPE,
'clientInfo', o.USER_COMMENT,
'customerRefNum', o.CUST_REF_NUM,
'date', o.ORDER_ENTRY_TIME,
'deliveryNotificationType', o.DELIVERY_NOTIFICATION_TYPE,
'fulfillmentEmail', o.FULFILLMENT_EMAIL,
'id', o.ORDER_AUTO_KEY,
'merchantAccountTransId', o.MERCHANT_ACCT_TRANS_ID,
'newDownloadsAvailable', o.NEW_DOWNLOADS_AVAIL,
'notificationMethod', o.NOTIFY_METHOD,
'orderComments', o.ORDER_COMMENTS,
'partnerAccountId', o.PARTNER_ACCOUNT_ID,
'partnerUserAccountId', o.PARTNER_USER_ACCOUNT_ID,
'partnerUserId', o.PARTNER_USERID,
'paymentMethod', o.PAYMENT_METHOD,
'sourceApp', o.SOURCE_APP,
'status', o.ORDER_STATUS,
'totalCost', o.TOTAL_COST,
'userid', o.USER_ID,
'lineItems', JSON_ARRAYAGG(
JSON_OBJECT(
'apn', i.APN,
'canBeGenerated', i.CAN_BE_GENERATED,
'completeTime', i.COMPLETE_TIME,
'confirmAllAddlLotsIn', i.CONF_ALL_ADDL_LOTS_IN,
'confirmIsCBRSOPA', i.CONF_IS_CBRS_OPA,
'confirmIsClaimOnParcel', i.CONF_IS_CLAIM_ON_PARCEL,
'confirmIsLOMC', i.CONF_IS_LOMC,
'confirmIsSFHA', i.CONF_IS_SFHA,
'countyFIPS', i.COUNTY_FIPS,
'customerRefNum', i.CUST_REF_NUM,
'dateAssigned', i.ASSIGNED_TIME,
'dateDue', i.DUE_TIME,
'emitPricingOnReport', i.EMIT_PRICING_ON_REPORT,
'fundId', i.FUND_ID,
'fundingType', i.FUNDING_TYPE,
'generationStatus', i.GENERATION_STATUS,
'id', i.LINE_ITEM_AUTO_KEY,
'issueCertificateToAddr', i.ISSUE_CERT_TO_ADDR,
'issueCertificateToName', i.ISSUE_CERT_TO,
'legalDescription', i.LEGAL_DESC,
'muniFIPS', i.MUNI_FIPS,
'orderId', i.ORDER_ID,
'owner', i.OWNER,
'parcelBlock', i.PARCEL_BLOCK,
'parcelDistrict', i.PARCEL_DISTRICT,
'parcelKey', i.PARCEL_KEY,
'parcelLot', i.PARCEL_LOT,
'parcelQualifier', i.PARCEL_QUALIFY,
'parsedMisc', i.PARSED_MISC,
'price', i.PRICE,
'processStartTime', i.PROCESS_START_TIME,
'productId', i.PRODUCT_ID,
'providerId', i.PROVIDER_ID,
'providerOKToPay', i.PROVIDER_OK_TO_PAY,
'quantity', i.QUANTITY,
'stateFIPS', i.STATE_FIPS,
'status', i.ITEM_STATUS,
'statusReason', i.ITEM_STATUS_REASON,
'streetAddress', i.STREET_ADDR,
'taxSearchIncludeUtils', i.TAX_SEARCH_INCLUDE_UTILS,
'townId', i.TOWN_ID,
'userComment', i.USER_COMMENT,
'workerId', i.WORKER_ID
)
)
) as order_json
FROM LINE_ITEMS i FORCE INDEX (PRIMARY)
LEFT JOIN ORDERS o ON o.order_auto_key = i.order_id
group by o.order_auto_key
where both the following are primary keys on their respective tables:
o.order_auto_key i.line_item_auto_key
I'm running mySQL 8.0.22
Could sure use some help with this one. Thanks.