# Could pt-archiver use a non-unique key as chunk index?

**URL:** <https://forums.percona.com/t/could-pt-archiver-use-a-non-unique-key-as-chunk-index/18620>\
**Category:** Percona Toolkit\
**Created:** [November 17, 2022, 6:33am UTC](https://forums.percona.com/t/could-pt-archiver-use-a-non-unique-key-as-chunk-index/18620 "2022-11-17T06:33:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luke03011](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/luke03011/32/16409_2.png) [@Luke03011](https://forums.percona.com/u/Luke03011)\
**Post date:** [November 17, 2022, 6:33am UTC](https://forums.percona.com/t/could-pt-archiver-use-a-non-unique-key-as-chunk-index/18620/1 "2022-11-17T06:33:50Z")

</div>

Assume I have a table defined as follows:

```auto
create table t(id int primary key, c1 int,c2 int, idx_c2(c2))engine=innodb

```

I want to archive data satisfying the following condition:

```auto
c2>=value

```

When I use `--dry-run` to run pt-archiver, it always picks up the primary key as the chunk index unless specifying the `i` part in the `--source` option.

```auto
SELECT /*!40001 SQL_NO_CACHE */ `id`,`c1`,`c2` FROM `test`.`t` FORCE INDEX(`idx_c2`) WHERE (c2>=6350) ORDER BY `c2` LIMIT 1000

```

Can this guarantee no duplicate rows to be archived? Because the SELECT generated has no guarantee it will not produce duplicate rows.

In fact, I expect the generated SELECT like the following:

```auto
SELECT /*!40001 SQL_NO_CACHE */ `id`,`c1`,`c2` FROM `test`.`t` FORCE INDEX(`idx_c2`) WHERE ((c2=? and id>? ) or c2>?) ORDER BY `c2`,`id` LIMIT 1000

```

and the ? is the last value for the last iteration.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [November 17, 2022, 2:18pm UTC](https://forums.percona.com/t/could-pt-archiver-use-a-non-unique-key-as-chunk-index/18620/2 "2022-11-17T14:18:10Z")

</div>

pt-archiver needs a unique index in order to “traverse” the table and to ensure that only 1 row is deleted after being inserted into the destination. If you tell pt-archiver to use a non-unique index, then there is no guarantee that you won’t have duplicates or delete the wrong data.

You might try creating an index on (PK, c2), which won’t give you direct uniqueness, but indirect since it uses the PK as a component.

---

<div class="post-metadata">

**Author:** ![Luke03011](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/luke03011/32/16409_2.png) [@Luke03011](https://forums.percona.com/u/Luke03011)\
**Post date:** [January 12, 2024, 2:58am UTC](https://forums.percona.com/t/could-pt-archiver-use-a-non-unique-key-as-chunk-index/18620/3 "2024-01-12T02:58:20Z")

</div>

Sorry it’s a long time when I post this ticket. But as the `idx_c2` actually contains the primary key `id` internally(In fact every secondary index will contain the primary key columns to make the indexed value is unique in the secondary index tree). So I am curious why `pt-archiver` does not use that part to guarantee the correctness.