MySQL Forums
Forum List  »  InnoDB

mySQL 8 - view query not using outer predicate
Posted by: Jack Crawford
Date: January 12, 2021 07:46PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL 8 - view query not using outer predicate
607
January 12, 2021 07:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.