MySQL Forums
Forum List  »  Optimizer & Parser

mySQL 8 - view query not using outer predicate
Posted by: Jack Crawford
Date: January 11, 2021 10:49AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL 8 - view query not using outer predicate
232
January 11, 2021 10:49AM


Sorry, only registered users may post in this forum.

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.