Description:
We run query to select most recent items from the table Test, with definition
create table Test
(
id int auto_increment primary key,
platform_id int not null,
item_id int not null,
status enum('NewAuto', 'NewManually', 'Approved', 'Rejected', 'OnProd', 'AutoApproved') default 'NewAuto' not null,
snap_id int default 0 not null,
type enum('Default', 'NonDefault') default 'Default' not null,
created_at timestamp default '0000-00-00 00:00:00' not null,
updated_at timestamp default '0000-00-00 00:00:00' not null,
constraint UNIQUE uniq_idx (item_id, platform_id, type, created_at),
index idx (item_id, platform_id, type, status, created_at)
)
collate=utf8mb4_general_ci;
INSERT INTO Test (platform_id, item_id, created_at, status, type, snap_id)
VALUES
(1, 3, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0),
(1, 2, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0),
(2, 3, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0),
(2, 2, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0),
(2, 111, '2019-05-17 14:43:29', 'NewAuto', 'Default', 0),
(2, 2, '2019-05-17 14:43:30', 'NewAuto', 'Default', 0),
(1, 2, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0),
(1, 111, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0);
And then run three different query, first one is original without any optimizer hints.
Original query contains more fields in outer select, to simplify I removed them (was SELECT t.id, t.platform_id, t.item_id, t.created_at, t.status, t.type, t.snap_id)
SELECT t.id
FROM Test t
JOIN (
SELECT item_id, MAX(created_at) AS created_at
FROM Test t
WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
GROUP BY item_id
) t2 ON
t.item_id = t2.item_id
AND t.created_at = t2.created_at
AND t.type = 'Default'
WHERE t.platform_id = 2;
+----+
| id |
+----+
| 6 |
| 3 |
+----+
--------------------------------
Disable uniq index for GROUP BY in subselect
SELECT t.id
FROM Test t
JOIN (
SELECT item_id, MAX(created_at) AS created_at
FROM Test t
IGNORE INDEX FOR GROUP BY (uniq_idx)
WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
GROUP BY item_id
) t2 ON
t.item_id = t2.item_id
AND t.created_at = t2.created_at
AND t.type = 'Default'
WHERE t.platform_id = 2;
+----+
| id |
+----+
| 6 |
| 3 |
| 5 |
+----+
--------------------------------
Use optimiser hint for disabling range optimizer for uniq index
SELECT t.id
FROM Test t
JOIN (
SELECT /*+ NO_RANGE_OPTIMIZATION(t uniq_idx) */ item_id, MAX(created_at) AS created_at
FROM Test t
WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
GROUP BY item_id
) t2 ON
t.item_id = t2.item_id
AND t.created_at = t2.created_at
AND t.type = 'Default'
WHERE t.platform_id = 2;
+----+
| id |
+----+
| 6 |
| 3 |
| 5 |
+----+
In the first select one row is missing with id 5.
How to repeat:
Create table and run queries above
Description: We run query to select most recent items from the table Test, with definition create table Test ( id int auto_increment primary key, platform_id int not null, item_id int not null, status enum('NewAuto', 'NewManually', 'Approved', 'Rejected', 'OnProd', 'AutoApproved') default 'NewAuto' not null, snap_id int default 0 not null, type enum('Default', 'NonDefault') default 'Default' not null, created_at timestamp default '0000-00-00 00:00:00' not null, updated_at timestamp default '0000-00-00 00:00:00' not null, constraint UNIQUE uniq_idx (item_id, platform_id, type, created_at), index idx (item_id, platform_id, type, status, created_at) ) collate=utf8mb4_general_ci; INSERT INTO Test (platform_id, item_id, created_at, status, type, snap_id) VALUES (1, 3, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0), (1, 2, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0), (2, 3, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0), (2, 2, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0), (2, 111, '2019-05-17 14:43:29', 'NewAuto', 'Default', 0), (2, 2, '2019-05-17 14:43:30', 'NewAuto', 'Default', 0), (1, 2, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0), (1, 111, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0); And then run three different query, first one is original without any optimizer hints. Original query contains more fields in outer select, to simplify I removed them (was SELECT t.id, t.platform_id, t.item_id, t.created_at, t.status, t.type, t.snap_id) SELECT t.id FROM Test t JOIN ( SELECT item_id, MAX(created_at) AS created_at FROM Test t WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default') GROUP BY item_id ) t2 ON t.item_id = t2.item_id AND t.created_at = t2.created_at AND t.type = 'Default' WHERE t.platform_id = 2; +----+ | id | +----+ | 6 | | 3 | +----+ -------------------------------- Disable uniq index for GROUP BY in subselect SELECT t.id FROM Test t JOIN ( SELECT item_id, MAX(created_at) AS created_at FROM Test t IGNORE INDEX FOR GROUP BY (uniq_idx) WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default') GROUP BY item_id ) t2 ON t.item_id = t2.item_id AND t.created_at = t2.created_at AND t.type = 'Default' WHERE t.platform_id = 2; +----+ | id | +----+ | 6 | | 3 | | 5 | +----+ -------------------------------- Use optimiser hint for disabling range optimizer for uniq index SELECT t.id FROM Test t JOIN ( SELECT /*+ NO_RANGE_OPTIMIZATION(t uniq_idx) */ item_id, MAX(created_at) AS created_at FROM Test t WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default') GROUP BY item_id ) t2 ON t.item_id = t2.item_id AND t.created_at = t2.created_at AND t.type = 'Default' WHERE t.platform_id = 2; +----+ | id | +----+ | 6 | | 3 | | 5 | +----+ In the first select one row is missing with id 5. How to repeat: Create table and run queries above