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.
I'm issuing 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.