1

We have a table with composite primary key with 1.6M rows where 2 different ways of writing the same query yields vastly different response times. Unfortunately our ORM creates the slow quiry variant. Any ideas how to make the where ... in variant quicker ?

update `rsa`.`patient` set `birth` = '20001102' where (`dossier`, `doc1`) in (('10279478', 'IX4')); [2073 ms]
update `rsa`.`patient` set `birth` = '20001102' where `dossier` = '10279478' and `doc1`= 'IX4'; [3 ms]

Here's the table definition for reference

-- rsa.patient definition

CREATE TABLE `patient` (
  `dossier` varchar(20) NOT NULL DEFAULT '',
  `rrn` varchar(11) DEFAULT NULL,
  `cmname` varchar(40) NOT NULL DEFAULT '',
  `name` varchar(35) NOT NULL DEFAULT '',
  `fname` varchar(35) NOT NULL DEFAULT '',
  `sex` char(1) NOT NULL DEFAULT '',
  `birth` date NOT NULL DEFAULT '0000-00-00',
  `address` varchar(35) NOT NULL DEFAULT '',
  `zip` varchar(10) NOT NULL DEFAULT '',
  `city` varchar(35) NOT NULL DEFAULT '',
  `doc1` varchar(10) NOT NULL DEFAULT '',
  `doc2` varchar(10) NOT NULL DEFAULT '',
  `doc3` varchar(10) NOT NULL DEFAULT '',
  `Viewed` int(1) unsigned NOT NULL DEFAULT 0,
  `owner` varchar(35) NOT NULL DEFAULT '',
  `species` varchar(20) NOT NULL DEFAULT '',
  `animal` int(1) unsigned NOT NULL DEFAULT 0,
  `cmowner` varchar(35) NOT NULL DEFAULT '',
  `ziekenfonds` varchar(6) NOT NULL DEFAULT '',
  `kg1` char(3) NOT NULL DEFAULT '',
  `kg2` char(3) NOT NULL DEFAULT '',
  `werkgever` varchar(17) NOT NULL DEFAULT '',
  `werknemer` varchar(17) NOT NULL DEFAULT '',
  `chipnr` varchar(15) NOT NULL DEFAULT '',
  `ebox` varchar(1) NOT NULL DEFAULT 'N',
  `doccle` varchar(1) NOT NULL DEFAULT 'N',
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
  `updatedAt` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deletedAt` timestamp NULL DEFAULT NULL,
  `declaredToCozoAt` timestamp NULL DEFAULT NULL,
  `cozoDeclarationLastResult` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`dossier`,`doc1`),
  KEY `name` (`name`,`fname`),
  KEY `cmname` (`cmname`),
  KEY `cmowner` (`cmowner`),
  KEY `rrn` (`rrn`),
  KEY `doc1` (`doc1`) USING BTREE,
  KEY `doc2` (`doc2`) USING BTREE,
  KEY `doc3` (`doc3`) USING BTREE,
  KEY `patient_createdAt_IDX` (`createdAt`) USING BTREE,
  KEY `patient_updatedAt_IDX` (`updatedAt`) USING BTREE,
  KEY `patient_deletedAt_IDX` (`deletedAt`) USING BTREE,
  KEY `patient_declaredToCozoAt_IDX` (`declaredToCozoAt`) USING BTREE,
  KEY `patient_animal_IDX` (`animal`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.