A prefix index, (body(20)), stores the first n characters, as TEXT and BLOB require; pick the shortest n whose COUNT(DISTINCT LEFT(col, n)) nears the full count, and expect no covering or sorting from it. A descending index (honored since 8.0) serves mixed sort directions. An invisible index is maintained but ignored, a reversible trial of a drop.
CREATE INDEX idx_body ON reviews (body);
SELECT id INTO @i FROM big_orders ORDER BY customer_id, ordered_at DESC LIMIT 1;
CREATE INDEX idx_cust_date_desc ON big_orders (customer_id, ordered_at DESC);
SELECT id INTO @i FROM big_orders ORDER BY customer_id, ordered_at DESC LIMIT 1;
ALTER TABLE big_orders ALTER INDEX idx_cust_date_desc INVISIBLE;ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length Query OK, 1 row affected (0.223 sec) Query OK, 0 rows affected (2.590 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.003 sec) Query OK, 0 rows affected (0.019 sec) Records: 0 Duplicates: 0 Warnings: 0
Without the descending index MySQL 524 sorted a million rows to return one (0.22 s); with it, the first entry is the answer, and a backward scan serves ORDER BY customer_id DESC, ordered_at. Hidden, it leaves the plans; /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ restores it for one statement. Watch the slow query log (Slow Query Log), then drop it or unhide it. A primary key cannot be hidden (error 3522).