SQL optimization case – change OR to Union

Raw SQL (average execution time 2s+)

SELECT DISTINCT
	sid
FROM
	test
WHERE
	cid = 123456
AND (
	(eid = 7890123)
	OR sid IN (
		SELECT
			sid
		FROM
			test
		WHERE
			cid = 123456
		GROUP BY
			sid
		HAVING
			COUNT(*) = 1
	)
)
Copy the code

Table 4.3 million + data volume, index as follows:

KEY idx_eid (eid)
KEY index_0 (cid,type)
KEY index_1 (sid,type,eid)
KEY idx_cs (cid,sid)
Copy the code

Optimized SQL (average execution time is about 400ms)

SELECT DISTINCT sid FROM test  WHERE cid = 900458 AND eid = 1021355 
union  
SELECT sid FROM test  WHERE cid = 900458 GROUP BY sid HAVING COUNT(*) = 1
Copy the code

Idx_cs = idx_cs = idx_cs = idx_CS = IDx_CS = IDX_CS = IDX_CS = IDX_CS = IDX_CS = IDX_CS