Comment 3 for bug 1452902

We have the same issue, we want the ascending optimization to use a non primary key index, but use the primary key to delete (which is pretty obvious when a primary key does exist)
The results of such operation is that the command ignores the --columns flag and retrieves all the fields and use them all for the delete operation. Now, mysql will use the primary key to delete but still the overhead of retrieving all the fields and sending them back on the delete operation is redundant.

Example of issue:

CREATE TABLE job_entity
    (
    pk BINARY(16) NOT NULL,
    pool_master SMALLINT UNSIGNED NOT NULL,
    full_id_iteration VARCHAR(50) NOT NULL,
    content MEDIUMBLOB NOT NULL,
    run_time MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    start_time BIGINT UNSIGNED NOT NULL,
    finish_time BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status TINYINT UNSIGNED NOT NULL,
    modified BIGINT UNSIGNED NOT NULL,
    exit_status SMALLINT,
    PRIMARY KEY (pk, pool_master))
ENGINE=InnoDB;

CREATE INDEX finish_time_index ON job_entity (finish_time) USING BTREE;
CREATE INDEX modified_index ON job_entity (modified) USING BTREE;
CREATE INDEX queries_index ON job_entity (pool_master,status,start_time) USING BTREE;
CREATE UNIQUE INDEX job_index ON job_entity (pool_master,full_id_iteration) USING BTREE;

##pt-archiver --host <hostname> --port <port> --user <user> --password <pass> --dry-run --progress 1 --statistics --noversion-check --purge --where 'pool_master=1 and finish_time > 0 and finish_time < 1000' --source D=db,t=job_entity,i=finish_time_index --nocheck-charset --ascend-first --columns 'pool_master,finish_time,full_id_iteration'

SELECT /*!40001 SQL_NO_CACHE */ `pool_master`,`finish_time`,`full_id_iteration`,`pk`,`content`,`run_time`,`start_time`,`status`,`modified`,`exit_status` FROM `nbodataservice`.`job_entity` FORCE INDEX(`finish_time_index`) WHERE (pool_master=1 and finish_time > 0 and finish_time < 1000) ORDER BY `finish_time` LIMIT 1
SELECT /*!40001 SQL_NO_CACHE */ `pool_master`,`finish_time`,`full_id_iteration`,`pk`,`content`,`run_time`,`start_time`,`status`,`modified`,`exit_status` FROM `nbodataservice`.`job_entity` FORCE INDEX(`finish_time_index`) WHERE (pool_master=1 and finish_time > 0 and finish_time < 1000) AND ((`finish_time` >= ?)) ORDER BY `finish_time` LIMIT 1
DELETE FROM `nbodataservice`.`job_entity` WHERE (`pk` = ? AND `pool_master` = ? AND ((? IS NULL AND `full_id_iteration` IS NULL) OR (`full_id_iteration` = ?)) AND `content` = ? AND `run_time` = ? AND `start_time` = ? AND `finish_time` = ? AND `status` = ? AND `modified` = ? AND ((? IS NULL AND `exit_status` IS NULL) OR (`exit_status` = ?))) LIMIT 1