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;