left join on null brings in 10 rows in 9.x

[jamie]

I’m not sure if this issue is specific to version 9.x—my colleague believes it is.

However, when we perform a one-to-one LEFT JOIN and the ID is NULL, it ends up bringing in the entire table, which drastically slows down the query.

Is there anything I can do to mitigate this, short of changing the relationship?

[rabii]

can you share your query.

[jamie]

she's a bit big but here it is, i also wonder how i would control the output order?

SELECT
account.id AS id,
account.assigned_user_id AS assignedUserId,
account.account_multi_type AS accountMultiType,
account.created_by_id AS createdById,
NULLIF(TRIM(CONCAT(IFNULL(user_createdBy.first_nam e, ''), ' ', IFNULL(user_createdBy.last_name, ''))), '') AS createdByName,
account.created_at AS createdAt,
account.accountstatus AS accountstatus,
account.country_shipping_id AS countryShippingId,
country_shipping.name AS countryShippingName,
account.website AS website,
account.name AS name,
(star_subscription.id IS NOT NULL) AS isStarred
FROM account
LEFT JOIN country_shipping
ON account.country_shipping_id = country_shipping.id AND country_shipping.deleted = 0 -- Shipping country info
LEFT JOIN user AS user_createdBy
ON account.created_by_id = user_createdBy.id AND user_createdBy.deleted = 0 -- Creator info
LEFT JOIN star_subscription
ON star_subscription.user_id = '6891c9c977f335f4b'
AND star_subscription.entity_type = 'Account'
AND star_subscription.entity_id = account.id -- Starred subscription info
WHERE NOT (
EXISTS (
SELECT
account_sub.id AS id,
account_sub.name AS name,
account_sub.deleted AS deleted,
account_sub.website AS website,
email_addresses.name AS emailAddress,
phone_numbers.name AS phoneNumber,
account_sub.type AS type,
account_sub.industry AS industry,
account_sub.sic_code AS sicCode,
account_sub.billing_address_street AS billingAddressStreet,
account_sub.billing_address_city AS billingAddressCity,
account_sub.billing_address_state AS billingAddressState,
account_sub.billing_address_country AS billingAddressCountry,
account_sub.billing_address_postal_code AS billingAddressPostalCode,
account_sub.shipping_address_street AS shippingAddressStreet,
account_sub.shipping_address_city AS shippingAddressCity,
account_sub.shipping_address_state AS shippingAddressState,
account_sub.shipping_address_country AS shippingAddressCountry,
account_sub.shipping_address_postal_code AS shippingAddressPostalCode,
account_sub.description AS description,
account_sub.created_at AS createdAt,
account_sub.modified_at AS modifiedAt
-- [additional columns truncated for brevity, but in full .sql file all columns are included]
FROM account AS account_sub
LEFT JOIN user AS user_createdBy_sub
ON account_sub.created_by_id = user_createdBy_sub.id AND user_createdBy_sub.deleted = 0
LEFT JOIN user AS user_modifiedBy_sub
ON account_sub.modified_by_id = user_modifiedBy_sub.id AND user_modifiedBy_sub.deleted = 0
LEFT JOIN country_shipping AS country_shipping_sub
ON account_sub.country_shipping_id = country_shipping_sub.id AND country_shipping_sub.deleted = 0
LEFT JOIN star_subscription AS star_subscription_sub
ON star_subscription_sub.entity_type = 'Account' AND star_subscription_sub.entity_id = account_sub.id
LEFT JOIN email_address AS email_addresses
ON email_addresses.id = account_sub.id AND email_addresses.deleted = 0
LEFT JOIN phone_number AS phone_numbers
ON phone_numbers.id = account_sub.id AND phone_numbers.deleted = 0
WHERE account_sub.id = account.id AND account_sub.deleted = 0
)
) AND account.deleted = 0
ORDER BY FIELD(account.accountstatus, 'Unsubscribed (Mailchimp)', 'Cleaned (Mailchimp Soft bounce)', 'Spam/Fraudulent', 'Dormant', 'Stop', 'Active', '') DESC, account.id ASC
LIMIT 0, 200;