Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.14
-
None
-
Debian 8 64bit
Description
On a query with several joins where the main table contains several million records (an on which the order by is applied), the optimizer chooses not to use an index on one of the join tables due to a low row count (only 2 rows in the table). The query subsequently scans ~1.5 million rows. Adding ~20 rows to the join table changes the optimizer choice and the resulting scan is then limited to ~82 rows.
The same query on MySQL 5.6 uses the index regardless of row count.
Query pasted below for reference (accounts_contacts initially contained only 2 rows):
SELECT
|
CASE |
WHEN jt8_favorite_link.id IS NOT NULL THEN |
1
|
ELSE
|
0
|
END my_favorite, |
CASE |
WHEN jt1_following_link.id IS NOT NULL THEN |
1
|
ELSE
|
0
|
END following, |
contacts.salutation full_name__salutation,
|
contacts.first_name full_name__first_name,
|
contacts.last_name full_name__last_name,
|
contacts.salutation salutation,
|
contacts.first_name first_name,
|
contacts.last_name last_name,
|
contacts.title title,
|
contacts_cstm.hug_last_branch_c hug_last_branch_c,
|
contacts_cstm.hug_status_c hug_status_c,
|
contacts_cstm.hug_postcode_c hug_postcode_c,
|
jt3_accounts.id account_id,
|
jt3_accounts.`name` account_name, |
jt5_email_addresses_primary.email_address email,
|
contacts.phone_work phone_work,
|
jt7_assigned_user_link.first_name assigned_user_name__first_name,
|
jt7_assigned_user_link.last_name assigned_user_name__last_name,
|
contacts.date_modified date_modified,
|
contacts.date_entered date_entered,
|
contacts_cstm.hug_credit_limit_c hug_credit_limit_c,
|
contacts_cstm.hug_customer_c hug_customer_c,
|
contacts_cstm.hug_creation_date_c hug_creation_date_c,
|
contacts.assigned_user_id assigned_user_id,
|
contacts.id id,
|
contacts.created_by created_by
|
FROM
|
contacts
|
LEFT JOIN sugarfavorites sf_contacts ON ( |
contacts.id = sf_contacts.record_id
|
AND sf_contacts.deleted = 0 |
AND sf_contacts.module = 'Contacts' |
AND sf_contacts.created_by = '1' |
)
|
LEFT JOIN subscriptions jt2_subscriptions ON ( |
contacts.id = jt2_subscriptions.parent_id
|
AND jt2_subscriptions.deleted = 0 |
AND jt2_subscriptions.parent_type = 'Contacts' |
AND jt2_subscriptions.created_by = '1' |
)
|
LEFT JOIN users jt1_following_link ON ( |
jt1_following_link.id = jt2_subscriptions.created_by
|
AND jt1_following_link.deleted = 0 |
)
|
LEFT JOIN users_cstm jt1_following_link_cstm ON ( |
jt1_following_link_cstm.id_c = jt1_following_link.id
|
)
|
{color:red}LEFT JOIN accounts_contacts jt4_accounts_contacts ON ( |
contacts.id = jt4_accounts_contacts.contact_id
|
AND jt4_accounts_contacts.deleted = 0 |
AND jt4_accounts_contacts.primary_account = '1' |
){color}
|
LEFT JOIN accounts jt3_accounts ON ( |
jt3_accounts.id = jt4_accounts_contacts.account_id
|
AND jt3_accounts.deleted = 0 |
)
|
LEFT JOIN email_addr_bean_rel jt6_email_addr_bean_rel ON ( |
contacts.id = jt6_email_addr_bean_rel.bean_id
|
AND jt6_email_addr_bean_rel.deleted = 0 |
AND jt6_email_addr_bean_rel.primary_address = '1' |
AND jt6_email_addr_bean_rel.bean_module = 'Contacts' |
)
|
LEFT JOIN email_addresses jt5_email_addresses_primary ON ( |
jt5_email_addresses_primary.id = jt6_email_addr_bean_rel.email_address_id
|
AND jt5_email_addresses_primary.deleted = 0 |
)
|
LEFT JOIN users jt7_assigned_user_link ON ( |
contacts.assigned_user_id = jt7_assigned_user_link.id
|
AND jt7_assigned_user_link.deleted = 0 |
)
|
LEFT JOIN users_cstm jt7_assigned_user_link_cstm ON ( |
jt7_assigned_user_link_cstm.id_c = jt7_assigned_user_link.id
|
)
|
LEFT JOIN users jt8_favorite_link ON ( |
jt8_favorite_link.id = sf_contacts.modified_user_id
|
AND jt8_favorite_link.deleted = 0 |
)
|
LEFT JOIN users_cstm jt8_favorite_link_cstm ON ( |
jt8_favorite_link_cstm.id_c = jt8_favorite_link.id
|
)
|
LEFT JOIN contacts_cstm ON contacts_cstm.id_c = contacts.id |
WHERE
|
contacts.deleted = 0
|
ORDER BY |
contacts.date_modified DESC, |
contacts.id DESC |
LIMIT 20,
|
21
|