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

Handler next limit scan the full table

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 10.0.7
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          Populate a sysbench table 

          handler `test`.`sbtest` open as t00000;                                                                                              
handler t00000 read `PRIMARY` >= (4995723) where `id` >= 4995723 and `id` <= 4995822 and (`id` between 4995723  and  4995822) limit 2;
+---------+---+---+----------------------------------------------------+
| id      | k | c | pad                                                |
+---------+---+---+----------------------------------------------------+
| 4995723 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 4995724 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+---------+---+---+----------------------------------------------------+
2 rows in set (0,00 sec)

handler t00000 read `PRIMARY` next         where `id` >= 4995723 and `id` <= 4995822 and (`id` between 4995723  and  4995822) limit 100;
[1/28/14 10:31:50 AM] svaroqui: | 4995822 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+---------+---+---+----------------------------------------------------+
98 rows in set (7,89 sec)
| Handler_read_next             | 5004278 |

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Observation: the value only goes crazy when limit reaches outside the range.

                Show
                elenst Elena Stepanova added a comment - Observation: the value only goes crazy when limit reaches outside the range.
                Hide
                Permalink
                serg Sergei Golubchik added a comment - - edited

                This is not a bug.

                Remember three facts:

                • HANDLER ... READ key NEXT traverses the index tree from the point, where the last key access left it.
                • There is no optimizer, it's direct access to the storage engine indexes.
                • WHERE clause is simply a filter that removes rows that would've been otherwise returned.

                So, what happens, you ask for the next 100 rows. Storage engine starts walking the index, finds and returns two 98 rows. But you've asked for a hundred. So it continues walking the index, but all other rows are rejected by the WHERE condition. There's no optimizer, HANDLER doesn't know that you're asking for a range, and it doesn't stop when it reaches the upper range limit, it simply goes until the very end of the index.

                Show
                serg Sergei Golubchik added a comment - - edited This is not a bug. Remember three facts: HANDLER ... READ key NEXT traverses the index tree from the point, where the last key access left it. There is no optimizer, it's direct access to the storage engine indexes. WHERE clause is simply a filter that removes rows that would've been otherwise returned. So, what happens, you ask for the next 100 rows. Storage engine starts walking the index, finds and returns two 98 rows. But you've asked for a hundred. So it continues walking the index, but all other rows are rejected by the WHERE condition. There's no optimizer, HANDLER doesn't know that you're asking for a range, and it doesn't stop when it reaches the upper range limit, it simply goes until the very end of the index.
                Hide
                Permalink
                stephane@skysql.com VAROQUI Stephane added a comment -

                Thanks i get it now !

                Show
                stephane@skysql.com VAROQUI Stephane added a comment - Thanks i get it now !

                  People

                  • Assignee:
                    elenst Elena Stepanova
                    Reporter:
                    stephane@skysql.com VAROQUI Stephane
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    5 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: