title: Will using %chin% to subset character columns of an auto-indexed data.table ever improve speed?
body: <blockquote>
    <p><strong>TLDR:</strong> When using more recent versions of <code>data.table</code> that use auto-indexing, is there any benefit from using <code>%chin%</code> to subset a data.table on character columns?</p>
  </blockquote>
  
  <p>In the past, using <code>%chin%</code> from <code>data.table</code> in place of <code>%in%</code> when subsetting on character vectors resulted in a significant speed-up. In newer versions of <code>data.table</code>, secondary indices are automatically created on non-key columns when sub-setting. The creation and usage of these indices appears to make any speed difference between <code>%chin%</code> and <code>%in%</code> irrelevant.</p>
  
  <p>Going forward, are there any cases where using <code>%chin%</code> to subset a data.table will still improve speed, or can I just use <code>%in%</code> in the future?</p>
  
  <hr>
  
  <p><em>Update: The conversation on <a href="https://github.com/Rdatatable/data.table/pull/2494" rel="noreferrer">PR#2494: Better subsetting optimization for compound queries</a> seems to support an understanding that when evaluated in the data.table calling environment, the execution methods of <code>%chin%</code> have been fundamentally changed.</em> </p>
  
  <p><em>For cases where the column used to subset the table more than once, performance will be drastically increased by auto-indexing, but when only used a single time (and thus not benefiting from the time spent generating the index), turning auto-indexing off will sometimes give marginally faster results.</em></p>
  
  <p><em>I'll leave this open for a couple days, but I may flesh this out into an answer for the sake of posterity.</em></p>
  
  <hr>
  
  <h2>Data Generation and Benchmarking</h2>
  
  <p>The data generated is made up of a randomly ordered combination of two unbalanced samples:</p>
  
  <ul>
  <li>10 million single character strings, 26 possible unique values</li>
  <li>1 million four character strings, 456,976 possible unique values</li>
  </ul>
  
  <p>The intent here is to be representative of non-normal variables that are dominated by a few common values, but have numerous less common possibilities.</p>
  
  <pre><code>library(data.table)
  library(microbenchmark)
  set.seed(1234)
  
  ## Create a vector of 1 million 4 character strings
  ## with 456,976 possible unique values 
  DiverseSize &lt;- 1e6
  Diverse &lt;- paste0(sample(LETTERS,DiverseSize,replace = TRUE),
                    sample(letters,DiverseSize,replace = TRUE),
                    sample(letters,DiverseSize,replace = TRUE),
                    sample(letters,DiverseSize,replace = TRUE))
  
  ## Create a vector of 10 million single character strings
  ## with 26 possible unique values
  CommonSize  &lt;- 1e7
  Common &lt;-  sample(LETTERS,CommonSize,replace = TRUE)
  
  ## Mix them into a data.table column, "x"
  DT1 &lt;- data.table(x = sample(c(Diverse,Common),size = CommonSize + DiverseSize, replace = FALSE))
  ## Make a deep copy to run independent comparisons
  DT2 &lt;- copy(DT1)
  </code></pre>
  
  <h2>Comparing <code>%in%</code> and <code>%chin%</code></h2>
  
  <p>When executing outside of the <code>data.table</code> environment, we still get a significant speed-up by using <code>%chin%</code>.</p>
  
  <pre><code>microbenchmark(
    Outside_chin = length(which(DT1[["x"]] %chin% c("Matt"))),
    Outside_in   = length(which(DT2[["x"]] %in% c("Matt"))),
    times = 1
  )
  </code></pre>
  
  <p>...</p>
  
  <pre><code>Unit: milliseconds
           expr      min       lq     mean   median       uq      max neval
   Outside_chin 254.5967 254.5967 254.5967 254.5967 254.5967 254.5967     1
     Outside_in 476.2117 476.2117 476.2117 476.2117 476.2117 476.2117     1
  </code></pre>
  
  <h2>Comparing <code>%in%</code> and <code>%chin%</code>with auto-indexing</h2>
  
  <pre><code>## Benchmarking -------
  ## Turn off Indices
  options(datatable.auto.index = FALSE)
  options(datatable.use.index = FALSE)
  
  ## Run without indices
  DT2[x %chin% c("Matt"), .N]
  DT1[x %in% c("Matt"), .N]
  
  ## Run Again
  DT2[x %chin% c("Matt"), .N]
  DT1[x %in% c("Matt"), .N]
  
  options(datatable.auto.index = TRUE)
  options(datatable.use.index = TRUE)
  
  ## First run builds indices and takes longer
  DT2[x %chin% c("Matt"), .N]
  DT1[x %in% c("Matt"), .N]
  
  ## Run again, benefiting from pre-built indices
  DT2[x %chin% c("Matt"), .N]
  DT1[x %in% c("Matt"), .N]
  </code></pre>
  
  <h2>Profiling Results</h2>
  
  <p>When using ProfVis to analyze the run-time of each expression, the following is apparent:</p>
  
  <ol>
  <li>Without auto-indexing, <code>%chin%</code> is faster and run-time is similar the first and second time.</li>
  <li>With auto-indexing, run-time is basically the same whether <code>%chin%</code> or <code>%in%</code> is used.</li>
  <li>While the auto-indexing makes the first execution slightly slower, the second call evaluates so fast profvis can't even catch it consistently with a 10 ms sample interval</li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/l1ZlI.png" rel="noreferrer"><img src="https://i.stack.imgur.com/l1ZlI.png" alt="%chin% %in% comparison"></a></p>
  
  <hr>
  
  <p>I am currently running data.table version 1.10.5, built 2018-03-17 07:30:06 UTC.</p>