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

regexp can push down range scan index when starting with a constant

    Details

      Description

      explain SELECT MAX(SUBSTRING(x0_.username FROM 11) * 1) AS sclr_0 FROM xcart_customers x0_ WHERE (x0_.username REGEXP 'tuttofare.[0-9]+') = 1;
      ------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ------------------------------------------------------------------------------------------+

      1 SIMPLE x0_ index NULL username 386 NULL 4196194 Using where; Using index

      ------------------------------------------------------------------------------------------+

      explain SELECT MAX(SUBSTRING(x0_.username FROM 11) * 1) AS sclr_0 FROM xcart_customers x0_ WHERE (x0_.username REGEXP 'tuttofare.[0-9]+')=1 and x0_.username like 'tuttofare%' ;
      ---------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ---------------------------------------------------------------------------------------+

      1 SIMPLE x0_ range username username 386 NULL 3359 Using where; Using index

      ---------------------------------------------------------------------------------------+

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              stephane@skysql.com VAROQUI Stephane
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: