sponsored links



No announcement yet.

Visual Explain "Indexes do not match" meaning?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Visual Explain "Indexes do not match" meaning?

    I am running this query
    SELECT *
      FROM table1 a
     INNER JOIN table2 b
        ON a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
    Both tables have a non-unique index on key1,2,3 in that order.

    However the SQL processor is not using the table1 index, it's doing a Table Scan (over 6.3 million rows) instead.

    Visual Explain, when I click on the Table Scan and look at "Information About the Plan Performed", I see:
    Information About the Plan Performed    
    Scrollable    Yes
    Plan Name    Table Scan
    Plan Step Type    Logic
    Reason Code    Indexes Do Not Match
    Plan Step Name    Node_40
    List of Indexes Optimized    MYLIB/TABLE1LA 11, MYLIB/TABLE1LB 4
    Interestingly, TABLE1LB is the logical that provides the index it should be using.

    The only thing of note, is that TABLE1LB is a DDS logical that specifies the keyword FIFO, which I have never seen before. The table has many other DDS logicals, but no actual SQL indexes (that I can find)

    Can anyone tell me what "Indexes do not match" means, and/or why the SQL processor may not be ignoring the index provided by the TABLE1LB logical?

  • #2
    Typo on the last line. Should be: "...why the SQL processor seems to be ignoring the index..."


    • #3
      Visual Explain is ingoring the index because where are no WHERE conditions to reduce the number of rows to be returned. So a table scan is performed. For each row in the first table the appropriate rows from the second tablel are joined. (BTW a binary radix tree index is only used if maximum 15-20% of the data in a table is returned. Otherwise an index access is to slow.
      You may try whether the index is used if you add an order by in the Key1, Key2, Key3 sequence.



      • #4
        Thanks Birgitta. So it sounds like "Indexes do not match" is the same as "Table scan cost is better"?

        However, I have now tried adding a Where field that would mean the query only returns 6 rows out of the 6 million:

        PHP Code:
        SELECT *
        FROM table1 a
        INNER JOIN table2 b
        ON a
        .key1 b.key1
        AND a.key2 b.key2
        AND a.key3 b.key3
        WHERE b
        .field4 100 and b.field4 50
        The Where clause matches 2000 rows out of 73000 in table2, and those rows match only 6 in table1. There is a key on field4.

        But it's still doing a table scan for the 6 million row table1?
        Last edited by jamief; 2 weeks ago.