DROP TABLE TEST;
CREATE TABLE IF NOT EXISTS TEST (
PK1 CHAR(1) NOT NULL,
PK2 VARCHAR NOT NULL,
PK3 VARCHAR NOT NULL,
PK4 UNSIGNED_LONG NOT NULL,
PK5 VARCHAR NOT NULL,
V1 VARCHAR,
V2 VARCHAR,
V3 UNSIGNED_LONG
CONSTRAINT state_pk PRIMARY KEY (
PK1,
PK2,
PK3,
PK4,
PK5
)
);
// Incorrect explain plan with un-ordered PKs
EXPLAIN SELECT V1 FROM TEST WHERE (PK1, PK5, PK2, PK3, PK4) IN (('A', 'E', 'N', 'T', 3), ('A', 'Y', 'G', 'T', 4));
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ | |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST ['A'] | null | null |
| SERVER FILTER BY (PK1, PK5, PK2, PK3, PK4) IN ([65,69,0,78,0,84,0,0,0,0,0,0,0,0,3],[65,89,0,71,0,84,0,0,0,0,0,0,0,0,4]) | null |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
// Correct explain plan with PKs in order
EXPLAIN SELECT V1 FROM TEST WHERE (PK1,PK2,PK3,PK4,PK5) IN (('A', 'E', 'N',3, 'T'),('A', 'Y', 'G', 4, 'T'));
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ | |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+
| CLIENT 1-CHUNK 2 ROWS 712 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 2 KEYS OVER TEST | 712 | |
+------------------------------------------+------------------------------------------+------------------------------------------+---------+