Slow lookup of part of primary key












0















I am struggling with query performance. I'm working with historical records of who has voted (or not) in an election. I have data for nearly 40 million ballots over 22 election. When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database. So I need a system that will let me merge new data into the existing data.



I have a table 'elections' with 22 rows:



CREATE TABLE `elections` (
`election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`election_type` enum('primary','general','special','board') NOT NULL,
`whose` enum('state','other') NOT NULL,
PRIMARY KEY (`election_id`),
UNIQUE KEY `date` (`date`,`whose`),
KEY `whose` (`whose`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;


And a table 'ballots' which is initially empty:



CREATE TABLE `ballots` (
`voter_id` int(10) unsigned NOT NULL,
`election_id` int(10) unsigned NOT NULL,
`status` enum('unreturned','invalid','valid') NOT NULL,
PRIMARY KEY (`voter_id`,`election_id`),
KEY `election_id` (`election_id`),
KEY `status` (`status`),
CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


My raw data obviously does not contain an election_id. I need to look up the election_id based on the date. I load my raw data initially into a 'temp_ballots' table which has ~40M rows:



CREATE TABLE `temp_ballots` (
`voter_id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
`status` enum('unreturned','invalid','valid') NOT NULL,
KEY `date` (`date`),
KEY `voter_id` (`voter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


And now, the part I struggle with. I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots. (Then I will drop temp_ballots.) The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow. I think I've indexed the appropriate columns. What am I doing wrong?



INSERT INTO `ballots` (voter_id, election_id, status)
SELECT voter_id, election_id, status
FROM elections NATURAL JOIN temp_ballots
WHERE whose = 'state'
ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;


I am using MariaDB 10.0.36, so I do not have the ANALYZE statement. But here is the output from EXPLAIN:



+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
| 1 | SIMPLE | elections | ref | date,whose | whose | 1 | const | 22 | Using index condition |
| 1 | SIMPLE | temp_ballots | ref | date | date | 3 | voters.elections.date | 1123 | |
+------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+








share



























    0















    I am struggling with query performance. I'm working with historical records of who has voted (or not) in an election. I have data for nearly 40 million ballots over 22 election. When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database. So I need a system that will let me merge new data into the existing data.



    I have a table 'elections' with 22 rows:



    CREATE TABLE `elections` (
    `election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `date` date NOT NULL,
    `election_type` enum('primary','general','special','board') NOT NULL,
    `whose` enum('state','other') NOT NULL,
    PRIMARY KEY (`election_id`),
    UNIQUE KEY `date` (`date`,`whose`),
    KEY `whose` (`whose`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;


    And a table 'ballots' which is initially empty:



    CREATE TABLE `ballots` (
    `voter_id` int(10) unsigned NOT NULL,
    `election_id` int(10) unsigned NOT NULL,
    `status` enum('unreturned','invalid','valid') NOT NULL,
    PRIMARY KEY (`voter_id`,`election_id`),
    KEY `election_id` (`election_id`),
    KEY `status` (`status`),
    CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    My raw data obviously does not contain an election_id. I need to look up the election_id based on the date. I load my raw data initially into a 'temp_ballots' table which has ~40M rows:



    CREATE TABLE `temp_ballots` (
    `voter_id` int(10) unsigned NOT NULL,
    `date` date NOT NULL,
    `status` enum('unreturned','invalid','valid') NOT NULL,
    KEY `date` (`date`),
    KEY `voter_id` (`voter_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    And now, the part I struggle with. I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots. (Then I will drop temp_ballots.) The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow. I think I've indexed the appropriate columns. What am I doing wrong?



    INSERT INTO `ballots` (voter_id, election_id, status)
    SELECT voter_id, election_id, status
    FROM elections NATURAL JOIN temp_ballots
    WHERE whose = 'state'
    ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;


    I am using MariaDB 10.0.36, so I do not have the ANALYZE statement. But here is the output from EXPLAIN:



    +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
    | 1 | SIMPLE | elections | ref | date,whose | whose | 1 | const | 22 | Using index condition |
    | 1 | SIMPLE | temp_ballots | ref | date | date | 3 | voters.elections.date | 1123 | |
    +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+








    share

























      0












      0








      0








      I am struggling with query performance. I'm working with historical records of who has voted (or not) in an election. I have data for nearly 40 million ballots over 22 election. When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database. So I need a system that will let me merge new data into the existing data.



      I have a table 'elections' with 22 rows:



      CREATE TABLE `elections` (
      `election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `date` date NOT NULL,
      `election_type` enum('primary','general','special','board') NOT NULL,
      `whose` enum('state','other') NOT NULL,
      PRIMARY KEY (`election_id`),
      UNIQUE KEY `date` (`date`,`whose`),
      KEY `whose` (`whose`)
      ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;


      And a table 'ballots' which is initially empty:



      CREATE TABLE `ballots` (
      `voter_id` int(10) unsigned NOT NULL,
      `election_id` int(10) unsigned NOT NULL,
      `status` enum('unreturned','invalid','valid') NOT NULL,
      PRIMARY KEY (`voter_id`,`election_id`),
      KEY `election_id` (`election_id`),
      KEY `status` (`status`),
      CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      My raw data obviously does not contain an election_id. I need to look up the election_id based on the date. I load my raw data initially into a 'temp_ballots' table which has ~40M rows:



      CREATE TABLE `temp_ballots` (
      `voter_id` int(10) unsigned NOT NULL,
      `date` date NOT NULL,
      `status` enum('unreturned','invalid','valid') NOT NULL,
      KEY `date` (`date`),
      KEY `voter_id` (`voter_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      And now, the part I struggle with. I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots. (Then I will drop temp_ballots.) The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow. I think I've indexed the appropriate columns. What am I doing wrong?



      INSERT INTO `ballots` (voter_id, election_id, status)
      SELECT voter_id, election_id, status
      FROM elections NATURAL JOIN temp_ballots
      WHERE whose = 'state'
      ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;


      I am using MariaDB 10.0.36, so I do not have the ANALYZE statement. But here is the output from EXPLAIN:



      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
      | 1 | SIMPLE | elections | ref | date,whose | whose | 1 | const | 22 | Using index condition |
      | 1 | SIMPLE | temp_ballots | ref | date | date | 3 | voters.elections.date | 1123 | |
      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+








      share














      I am struggling with query performance. I'm working with historical records of who has voted (or not) in an election. I have data for nearly 40 million ballots over 22 election. When I get additional data in the future, the oldest elections will drop off of the new data set, but I want to retain that information in my own database. So I need a system that will let me merge new data into the existing data.



      I have a table 'elections' with 22 rows:



      CREATE TABLE `elections` (
      `election_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `date` date NOT NULL,
      `election_type` enum('primary','general','special','board') NOT NULL,
      `whose` enum('state','other') NOT NULL,
      PRIMARY KEY (`election_id`),
      UNIQUE KEY `date` (`date`,`whose`),
      KEY `whose` (`whose`)
      ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;


      And a table 'ballots' which is initially empty:



      CREATE TABLE `ballots` (
      `voter_id` int(10) unsigned NOT NULL,
      `election_id` int(10) unsigned NOT NULL,
      `status` enum('unreturned','invalid','valid') NOT NULL,
      PRIMARY KEY (`voter_id`,`election_id`),
      KEY `election_id` (`election_id`),
      KEY `status` (`status`),
      CONSTRAINT `ballots_ibfk_1` FOREIGN KEY (`election_id`) REFERENCES `elections` (`election_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      My raw data obviously does not contain an election_id. I need to look up the election_id based on the date. I load my raw data initially into a 'temp_ballots' table which has ~40M rows:



      CREATE TABLE `temp_ballots` (
      `voter_id` int(10) unsigned NOT NULL,
      `date` date NOT NULL,
      `status` enum('unreturned','invalid','valid') NOT NULL,
      KEY `date` (`date`),
      KEY `voter_id` (`voter_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      And now, the part I struggle with. I want to take the data from temp_ballots, look up the correct election_id, and insert it into ballots. (Then I will drop temp_ballots.) The following operation is taking hours, and I don't know when it might finish, and I don't understand why this is slow. I think I've indexed the appropriate columns. What am I doing wrong?



      INSERT INTO `ballots` (voter_id, election_id, status)
      SELECT voter_id, election_id, status
      FROM elections NATURAL JOIN temp_ballots
      WHERE whose = 'state'
      ON DUPLICATE KEY UPDATE ballots.status = temp_ballots.status;


      I am using MariaDB 10.0.36, so I do not have the ANALYZE statement. But here is the output from EXPLAIN:



      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+
      | 1 | SIMPLE | elections | ref | date,whose | whose | 1 | const | 22 | Using index condition |
      | 1 | SIMPLE | temp_ballots | ref | date | date | 3 | voters.elections.date | 1123 | |
      +------+-------------+--------------+------+---------------+-------+---------+-----------------------+------+-----------------------+






      mariadb





      share












      share










      share



      share










      asked 4 mins ago









      Kyle MarkleyKyle Markley

      12




      12






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228147%2fslow-lookup-of-part-of-primary-key%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228147%2fslow-lookup-of-part-of-primary-key%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh