[MDEV-14855] UNION subquery optimization - Jira

XML

Word

Printable

The optimizer is not able to handle UNION subqueries.

For example :

SELECT * FROM user

WHERE user.id IN (

	SELECT idcontact FROM contact WHERE iduser = %s

	UNION

	SELECT iduser FROM contact WHERE idcontact = %s);

is extremly slow, because the main query is of type ALL instead of using index (if I run two queries separately, its extremly fast).

Here is the result of EXPLAIN :

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	user	ALL	NULL	NULL	NULL	NULL	570642	Using where
2	DEPENDENT SUBQUERY	contact	eq_ref	PRIMARY,idcontact	PRIMARY	8	const,func	1	Using index
3	DEPENDENT UNION	contact	eq_ref	PRIMARY,idcontact	PRIMARY	8	func,const	1	Using index
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL

(NOTE: the second line give a wrong numbers of rows, it's greater for me)

And this is the EXPLAIN for a simpler query without union :

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	contact	ref	PRIMARY,idcontact	PRIMARY	4	const	35	Using index
1	PRIMARY	user	eq_ref	PRIMARY	PRIMARY	4	contact.idcontact	1

Using two clauses joined with OR doesn't work either.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.