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

Server selecting the wrong index

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.2.12
    • N/A
    • Optimizer
    • None
    • Galera Cluster running on CentOS 7 virtual machines.

    Description

      I created an compound index with use_id and asd_date_start. Theres also a index for use_id, which i removed later on since its redundant. The issue is that after i created the compound index i did a explain and the result was that it would use the compound index. When the query ran it did not use the compound index, it used the single index on use_id.

      Indices:
      KEY `pro_id` (`use_id`),
      KEY `agent_state_detail_idx1` (`asd_date_start`) USING BTREE,
      KEY `idx_agent_state_detail_asd_date_end` (`asd_date_end`),
      KEY `idx_agent_state_detail_use_id_asd_date_start` (`use_id`,`asd_date_start`)

      Query:
      select agentstate0_.asd_id as asd_id1_1_, agentstate0_.asd_created_date as asd_crea2_1_, agentstate0_.asd_duration as asd_dura3_1_, agentstate0_.asd_duration_pending as asd_dura4_1_, agentstate0_.asd_date_end as asd_date5_1_, agentstate0_.asd_event_type as asd_even6_1_, agentstate0_.iwo_id as iwo_id12_1_, agentstate0_.iwo_id_pending as iwo_id_13_1_, agentstate0_.asd_need_updated as asd_need7_1_, agentstate0_.asd_place as asd_plac8_1_, agentstate0_.asd_reason_code as asd_reas9_1_, agentstate0_.asd_reason_code_description as asd_rea10_1_, agentstate0_.asd_date_start as asd_dat11_1_, agentstate0_.use_id as use_id14_1_ from agent_state_detail agentstate0_ where agentstate0_.use_id='01960da6-3c27-4c3f-aab1-6f25e1ce1056' and (agentstate0_.asd_date_start between TIMESTAMP'2018-02-27 08:55:00' and TIMESTAMP'2018-02-27 13:30:59.999000' or agentstate0_.asd_date_end between TIMESTAMP'2018-02-27 08:55:00' and TIMESTAMP'2018-02-27 13:30:59.999000' or (TIMESTAMP'2018-02-27 08:55:00' between agentstate0_.asd_date_start and agentstate0_.asd_date_end) and (TIMESTAMP'2018-02-27 13:30:59.999000' between agentstate0_.asd_date_start and agentstate0_.asd_date_end)) and (agentstate0_.iwo_id is null or agentstate0_.asd_duration_pending>0) order by agentstate0_.asd_date_start ASC

      Attachments

        1. analyze.csv
          3 kB
        2. explain.csv
          0.2 kB
        3. server.cnf
          3 kB

        Activity

          People

            alice Alice Sherepa
            obissick Oren Bissick (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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