# TTL with secondary index **URL:** \ **Category:** General\ **Created:** [January 13, 2021, 5:45pm UTC](https://forum.yugabyte.com/t/ttl-with-secondary-index/928 "2021-01-13T17:45:11Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![epratt-yb](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/epratt-yb/32/293_2.png) [@epratt-yb](https://forum.yugabyte.com/u/epratt-yb)\ **Post date:** [January 13, 2021, 5:45pm UTC](https://forum.yugabyte.com/t/ttl-with-secondary-index/928/1 "2021-01-13T17:45:11Z")
[Question posted by a user on [YugabyteDB Community Slack](https://www.yugabyte.com/slack) ] We need to expire data using a TTL with a table that uses a secondary index. How would we remove data that currently expires using a TTL, but can’t due to the need for a secondary index? ---
**Author:** ![epratt-yb](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/epratt-yb/32/293_2.png) [@epratt-yb](https://forum.yugabyte.com/u/epratt-yb)\ **Post date:** [January 13, 2021, 5:48pm UTC](https://forum.yugabyte.com/t/ttl-with-secondary-index/928/2 "2021-01-13T17:48:28Z")
You will need to do explicit deletes. 1. These deletes can be point deletes, or say range deletes within a partition key. 2. If they are range deletes within a partition key, there was an issue where we were scanning the whole partition key, instead of just the range. This was recently addressed and backported to 2.1.x/2.2/etc. around last week. 3. If using range deletes, or point deletes, it would be still be better if they can control the number of rows touched by the range or the batch sizes – we generally don’t prefer to do very large multi-row operations. So batch size of 256 or 512 would be ideal. 4. They can write a utility to make the scan/delete job run in parallel using a set of worker threads. This can be modeled similar to how we do “row counts” in parallel using `partition_hash` On #4, recently, I had responded thus, to a community user question:Using the [partition\_hash](https://docs.yugabyte.com/latest/api/ycql/expr_fcall/#partition-hash-function) function (YCQL equivalent of the `token` function in Apache Cassandra) to split the 0…64K hash space of a table is a reliable way (stable API) to partition the work among a set of worker tasks.Here’s an example python program that uses the same concept to count the total number of rows in a table using a configurable number of worker threads.[https://gist.github.com/kmuthukk/5899f38a147e2ccd36ac8fc2a81ca5c7And](https://gist.github.com/kmuthukk/5899f38a147e2ccd36ac8fc2a81ca5c7And) a Go version of the same:[https://github.com/yugabyte/yb-tools/tree/main/ycrc](https://github.com/yugabyte/yb-tools/tree/main/ycrc) #2 was addressed in issue: > > > Original issue was that \`SELECT count(\*) FROM T WHERE h='h1' and timestamp \>= '2…010-4-2' and timestamp \<= '2010-4-2 6:00';\` is working fine, but there is a timeout error for \`DELETE FROM T WHERE h='h1' and timestamp \>= '2010-4-2' and timestamp \<= '2010-4-2 6:00';\`. > > I’ve found the following regarding the difference in SELECT vs DELETE execution: > \- SELECT: > \`\`\` > I1211 14:02:26.604681 11840 doc\_rowwise\_iterator.cc:540\] Initializing iterator direction: FORWARD > I1211 14:02:26.604789 11840 doc\_rowwise\_iterator.cc:544\] DocKey Bounds DocKey(0x0011, \["\\"h1\\""\], \[1270188000000000\]), DocKey(0x0011, \["\\"h1\\""\], \[1270166400000000, +Inf\]) > I1211 14:02:26.605340 11840 doc\_rowwise\_iterator.cc:575\] yb::Status yb::docdb::DocRowwiseIterator::DoInit(const T&) \[with T = yb::docdb::DocQLScanSpec\] Seeking to DocKey(0x0011, \["\\"h1\\""\], \[1270188000000000\]) > \`\`\` > \- DELETE: > \`\`\` > I1211 14:02:41.112335 11829 doc\_rowwise\_iterator.cc:540\] Initializing iterator direction: FORWARD > I1211 14:02:41.112390 11829 doc\_rowwise\_iterator.cc:544\] DocKey Bounds DocKey(0x0011, \["\\"h1\\""\], \[\]), DocKey(0x0011, \["\\"h1\\""\], \[+Inf\]) > I1211 14:02:41.112463 11829 doc\_rowwise\_iterator.cc:575\] yb::Status yb::docdb::DocRowwiseIterator::DoInit(const T&) \[with T = yb::docdb::DocQLScanSpec\] Seeking to DocKey(0x0011, \["\\"h1\\""\], \[\]) > \`\`\` > > So, \`DELETE\` doesn’t use timestamp range from where clause and iterating other all keys having \`hash\_key == 'h1'\`. > And the reason is that \`SELECT\` uses \`QLReadOperation::Execute\` that initializes scan spec in this way: > \`\`\` > RETURN\_NOT\_OK(ql\_storage.BuildYQLScanSpec( > request\_, read\_time, schema, read\_static\_columns, static\_projection, &spec, > &static\_row\_spec)); > \`\`\` > > While \`DELETE\` is using \`QLWriteOperation::ReadColumns\` that only uses the hash key from "where" clause to initialize scan spec: > \`\`\` > if (hashed\_doc\_key\_) { > DocQLScanSpec spec(\*static\_projection, \*hashed\_doc\_key\_, request\_.query\_id()); // HERE > DocRowwiseIterator iterator(\*static\_projection, \*schema\_, txn\_op\_context\_, > data.doc\_write\_batch-\>doc\_db(), > data.deadline, data.read\_time); > RETURN\_NOT\_OK(iterator.Init(spec)); > \`\`\` > > Another thing to address here is that during \`DELETE\` execution with distributed transactions we should only generate provisional records for partial range key, not for the whole hash key, otherwise we would have way more transaction conflicts.