mariadb – Slow search of part of the primary key

I have problems with query performance. I work with historical records of who participated in an election (or not). I have data for nearly 40 million ballots in 22 elections. If I receive more data in the future, the oldest choices will be removed from the new record, but I would like to keep this information in my own database. So I need a system that allows me to merge new data with the existing data.

I have a table "choices" with 22 lines:

CREATE TABLE "elections" (
`choice_id` int (10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
"choice_type" enum ("primary", "general", "special", "board") NOT NULL,
"whom" enum ("state", "other") NOT NULL,
PRIMARY KEY ("election_id"),
UNIQUE KEY `date` (` date`, `which`),
KEY "WHO" ("WHO")
) ENGINE = InnoDB AUTO_INCREMENT = 32 DEFAULT CHARSET = Latin1;

And a table "ballot", which is initially empty:

CREATE TABLE "Ballot" (
`voter_id` int (10) unsigned NOT NULL,
`choice_id` int (10) unsigned NOT NULL,
`status` enum (& # 39; not returned & # 39 ;, & # 39; invalid & # 39 ;, & 39; valid & # 39;) NOT NULL,
PRIMARY KEY (`voter_id`,` choice_id`),
KEY `choice_id` (` choice_id`),
KEY `status` (` status`),
CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (` wahl_id`) REFERENCES `choose` (` wahl_id`)
) ENGINE = InnoDB DEFAULT CHARSET = Latin1;

My raw data obviously does not contain a choice_id. I have to look up the choice_id based on the date. I initially load my raw data into a & # 39; temp_ballots & # 39; table with ~ 40 million rows:

CREATE TABLE `temp_ballots` (
`voter_id` int (10) unsigned NOT NULL,
`date` date NOT NULL,
`status` enum (& # 39; not returned & # 39 ;, & # 39; invalid & # 39 ;, & 39; valid & # 39;) NOT NULL,
KEY `date` (` date`),
KEY `voter_id` (` voter_id`)
) ENGINE = InnoDB DEFAULT CHARSET = Latin1;

And now the part I'm struggling with. I want to extract the data from temp_ballots, look up the correct choice_id, and insert them into the voting lists. (Then I'll delete temp_ballots.) The following operation takes hours, and I do not know when it could be ended, and I can not understand why this is slow. I think I have indexed the corresponding columns. What am I doing wrong?

INSERT IN "ballot" (voter_id, wahl_id, status)
SELECT voter_id, choice_id, status
CHOOSE NATURAL JOIN temp_ballots
WO, whose = "state"
ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;

I'm using MariaDB 10.0.36, so I do not have the ANALYZE statement. But here is the output of EXPLAIN:

+ ------ + ------------- + -------------- + ------ + ------ --------- + ------- + --------- + ---------------------- - + ------ + ----------------------- +
| id | select_type | Table | Type | possible_buttons | Key | key_len | ref | Lines | Extra |
+ ------ + ------------- + -------------- + ------ + ------ --------- + ------- + --------- + ---------------------- - + ------ + ----------------------- +
| 1 | SIMPLE | Elections | ref | Date whose | whose | 1 | const | 22 | Using the index condition |
| 1 | SIMPLE | temp_ballots | ref | Date | Date | 3 | Voters.Select.Date | 1123 | |
+ ------ + ------------- + -------------- + ------ + ------ --------- + ------- + --------- + ---------------------- - + ------ + ----------------------- +