Try to run an explain on the query to see how mysql optimizes it.
Also have a look at your system resources while the query runs to see if
you have a problem with memory/swapping.
I am not sure (since i've never used union), but my guess is that mysql
uses temporary tables for this, so maybe tuning server parameters like
tmp_table_size can help.
PRASHANT N wrote:
hi,
i have attached my geometric query which is very slow. how to optimize this
query
regards
shann
_______________________________________________________________
Hot new product - Spider Networks introduces stunning online ePortfolio solution for students and teachers
http://www.spider-networks.net/solutions/eportfolio.html
------------------------------------------------------------------------
select A.name,A.district,x(GeomFromText(AsText(A.geo))) as
x,y(GeomFromText(AsText(A.geo))) as
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
11.83884)')))))) as Distance FROM (select geo,name,district from
cities_point union all select geo,name,district from cities_font_point
union all select geo,name,district from State_Highways_point union all
select geo,name,district from Other_Roads_point union all select
geo,name,district from Major_Roads_point union all select
geo,name,district from Vet_Clinics_point union all select
geo,name,district from University_point union all select
geo,name,district from Tourist_Info_point union all select
geo,name,district from Temples_point union all select geo,name,district
from Taxi_Stands_point union all select geo,name,district from
Stadiums_point union all select geo,name,district from
Sports_Clubs_point union all select geo,name,district from
Shops_WhiteGds_point union all select geo,name,district from
Shops_Sports_point union all select geo,name,district from
Shops_RealEstate_point union all select geo,name,district from
Shops_Music_point union all select geo,name,district from
Shops_Misc_point union all select geo,name,district from Shops_LPG_point
union all select geo,name,district from Shops_Jewellery_point union all
select geo,name,district from Shops_Furnt_point union all select
geo,name,district from Shops_Footwear_point union all select
geo,name,district from Shops_Computer_point union all select
geo,name,district from Shops_Chemists_point union all select
geo,name,district from Shops_Bakery_point union all select
geo,name,district from Shops_Apparel_point union all select
geo,name,district from Shopping_Ctrs_point union all select
geo,name,district from Services_Travel_point union all select
geo,name,district from Services_Professional_point union all select
geo,name,district from Services_Financial_point union all select
geo,name,district from Service_Stations_point union all select
geo,name,district from Schools_point union all select geo,name,district
from Restaurants_point union all select geo,name,district from
Religious_Pls_Oth_point union all select geo,name,district from
Railway_Stations_point union all select geo,name,district from
Railway_Reservations_point union all select geo,name,district from
PreSchools_point union all select geo,name,district from
PostOffices_point union all select geo,name,district from
PoliceStations_point union all select geo,name,district from
PoliceChaukis_point union all select geo,name,district from
PetrolPumps_point union all select geo,name,district from Parks_point
union all select geo,name,district from OtherInstt_point union all
select geo,name,district from Offices_point union all select
geo,name,district from Office_Airlines_point union all select
geo,name,district from Museums_point union all select geo,name,district
from Mosques_point union all select geo,name,district from Misc_point
union all select geo,name,district from Libraries_point union all select
geo,name,district from LevelCrossing_point union all select
geo,name,district from Industries_point union all select
geo,name,district from Hotels_point union all select geo,name,district
from Hostels_point union all select geo,name,district from
Hospitals_point union all select geo,name,district from
Historical_Pls_point union all select geo,name,district from
Gymnasiums_point union all select geo,name,district from Gurdwaras_point
union all select geo,name,district from Graveyards_point union all
select geo,name,district from GovtOffices_point union all select
geo,name,district from Dispensaries_point union all select
geo,name,district from Discotheques_point union all select
geo,name,district from Diagnostic_Ctrs_point union all select
geo,name,district from CyberCafes_point union all select
geo,name,district from Cultural_Centres_point union all select
geo,name,district from Crematory_point union all select
geo,name,district from Couriers_point union all select geo,name,district
from Computer_Instt_point union all select geo,name,district from
Colleges_point union all select geo,name,district from Clubs_point union
all select geo,name,district from Clinics_point union all select
geo,name,district from Cinemas_point union all select geo,name,district
from Church_point union all select geo,name,district from
BusTerminals_point union all select geo,name,district from
BusStops_point union all select geo,name,district from BusDepot_point
union all select geo,name,district from Buildings_point union all select
geo,name,district from Bowling_Alleys_point union all select
geo,name,district from Book_Stores_point union all select
geo,name,district from Blood_Banks_point union all select
geo,name,district from Billiard_N_Pool_point union all select
geo,name,district from Beauty_Parlours_point union all select
geo,name,district from Bars_point union all select geo,name,district
from Banquet_Halls_point union all select geo,name,district from
Banks_point union all select geo,name,district from
Bangalore_Landmarks_point_point union all select geo,name,district from
Auto_Dealers_point union all select geo,name,district from
Auditoria_point union all select geo,name,district from
Art_Galleries_point union all select geo,name,district from
Apartments_point union all select geo,name,district from Ambulance_point
union all select geo,name,district from ATMs_point) As A �where
MBRContains(GeomFromText(AsText(Envelope(GeomFromText('LineString(68.681735 �
22.832847,71.698254 �23.871751 )')))), GeomFromText(AsText(geo))) �= 1 �
order by Distance ASC LIMIT 1;
------------------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
