MySQL Bugs: #119190: Comparing the result of EXISTS(subquery) with a constant severely reduces query performance

Bug #119190	Comparing the result of EXISTS(subquery) with a constant severely reduces query performance
Submitted:	19 Oct 2025 12:28	Modified:	20 Jan 10:34
Reporter:	Power Gamer	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Create tables:

CREATE TABLE `t1` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`a_id` INT NOT NULL,
	`b_id` INT NOT NULL,
	`v_id` INT NOT NULL,
	`p_id` INT NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `t1_idx1` (`a_id`, `b_id`, `p_id`, `v_id`, `id`)
);

CREATE TABLE `t2` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`a_id` INT NOT NULL,
	`b_id` INT NOT NULL,
	`v_id` INT NOT NULL,
	`p_id` INT NOT NULL,
	`j_id` INT NOT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_t1_t2` FOREIGN KEY (`a_id`, `b_id`, `p_id`, `v_id`, `j_id`) REFERENCES `t1` (`a_id`, `b_id`, `p_id`, `v_id`, `id`)
);

Execute attached testdata.sql.

Query #1:
SELECT COUNT(*) FROM t1 WHERE (EXISTS (SELECT * FROM t2 WHERE t2.j_id=t1.id AND t2.a_id=1));
Executes super fast.

Query #2:
SELECT COUNT(*) FROM t1 WHERE (EXISTS (SELECT * FROM t2 WHERE t2.j_id=t1.id AND t2.a_id=1)) = 1;
Executes much much slower.

Expected results.
Query #2 runs as fast as query #1.

How to repeat:
See description.

Suggested fix:
Comparing the result of EXISTS() with any constant should not affect query execution performance.
I.e., the condition of the form "(EXISTS (subquery)) = 1" should be optimized into "(EXISTS (subquery))" and run as fast.
Analogous, the condition of the form "(EXISTS (subquery)) = 0" should be optimized into "(NOT EXISTS (subquery))" and run as fast.

The requested optimization is not implemented, but the work-around should be pretty straight-forward, so we see no reason to fix this.

"but the work-around should be pretty straight-forward"
The workaround is only straight-forward as long as you are in complete control of SQL being generated (i.e. write SQL query yourself). But it is much less so when the SQL query is generated by a library in a high-level scripting language.

But more importantly, the _troubleshooting_ of the issue is _not_ straight-forward at all. No sane person will ever expect the performance of a query to severely tank due to comparison with a constant value which logically changes nothing in the query (and of course, this "quirk" of MySQL is not documented anywhere).

And also, other DBMS (such as PostgreSQL for example) do not have this issue, and both queries (from my original report) execute equally fast.

Taking all of the above into account, I think you should reevaluate your decision about implementing required optimizations in MySQL.