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

INDEX Optimization - Virtual Column, Dynamic Column and Index optimization

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • None
    • None

    Description

      Hi guys,
      i was trying to work around MDEV-4414 with virtual column + index
      but, check my problem... here the table definition:

      create table t (
         linha_digitavel varchar(54) not null default '',
         tmp_substr char(37) as (SUBSTRING(linha_digitavel,1,37)) persistent,
         key linha_key (linha_digitavel),
         key tmp_substr_key (tmp_substr)
      );

      now the problem... i have about 50k rows in this table..

      explain select * from t where tmp_substr='abc'

      result => using index condition, key = 'tmp_substr_key', 1 rows

      explain select * from t where SUBSTRING(linha_digitavel,1,37)='abc'

      result => using where, full table scan

      —
      well i see two kinds of optimization here...
      1)the MDEV-4414, where substring(?,1,?) could use index "key linha_key (linha_digitavel)"
      2)the "SUBSTRING(linha_digitavel,1,37)" could be rewrite as "tmp_substr"

      (2) is the MDEV feature requested here

      it give DBA an oportunity to optimize closed systems, that we can't change source code, just tables...

      think about a sloowww query that could be optimized with a virtual column + index + sql rewrite ! ok many job... but it's a nice feature
      think about determinist functions (md5 for example) that could be optimized just replacing the "determinist_function(some_parameters)" to the virtual column, and the option to index it and make it really faster!

      think about MongoDB like features... a schema less table with index on fields that may or may not exists based on LONGBLOB fields + Dynamic Column + Virtual Index, i could say goodbye mongoDB for small systems (ok the map reduce is a parallel operation with high query performace, but it's not mysql =] )

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              rspadim roberto spadim
              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.