expat
Nov 23rd 2004, 1:42 am
whilst scanning through the latest MySql docu to fing tweaks I stumbled across UNION (avail after 4.0.12 or so) before this required tem tables...
I do have some queries where I can't index fulltext as I need the raw update speed as well thus have to run some complex queries across multiple fields with and and or.
now instead of using the unoptimised or function I use UNION
WHERE (ww_apt_f like '%$ww_from%' AND ww_apt_t like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_f_cty like '%$ww_from%' AND ww_apt_t_cty like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_t like '%$ww_from%' AND ww_apt_f like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_t_cty like '%$ww_from%' AND ww_apt_f_cty like '%$ww_to%')
checks for routes allowing entry of partial city or country names also throws up reverse routes ....
It also has DISTINCT incuded as default eliminating anay duplicate records from the result set and running this across 250.000 records shows a 20% speed improvement and the code is cleaner..
M
I do have some queries where I can't index fulltext as I need the raw update speed as well thus have to run some complex queries across multiple fields with and and or.
now instead of using the unoptimised or function I use UNION
WHERE (ww_apt_f like '%$ww_from%' AND ww_apt_t like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_f_cty like '%$ww_from%' AND ww_apt_t_cty like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_t like '%$ww_from%' AND ww_apt_f like '%$ww_to%')
UNION
SELECT * FROM `whoandwhere` WHERE (ww_apt_t_cty like '%$ww_from%' AND ww_apt_f_cty like '%$ww_to%')
checks for routes allowing entry of partial city or country names also throws up reverse routes ....
It also has DISTINCT incuded as default eliminating anay duplicate records from the result set and running this across 250.000 records shows a 20% speed improvement and the code is cleaner..
M