Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10157

Optimizer not using index on join table containing only a few rows of data

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.14
    • Fix Version/s: 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            joncutting Jonathan Cutting
            Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.