sql - MySQL query optimization using coalesce -
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1)
select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc;
~~~
query 2 ------------------
~~~
select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using (feature_code, version, platform_id) left outer join platform using(platform_id) left outer join feature_name using(feature_code) ;
it seems way generates release_exceptions column big difference in speeds. coworker wrote second one, unable explain why it's faster.
http://imgur.com/dxluo6s,noryv7v#0
first image first query (note - feature_code, platform_id, , version added indexes after posting, results in first 1 being faster now)
its not coalse that's causing speed up. it's removal of sub query. first runs query per row
the second single sub query
Comments
Post a Comment