Hello Prashant,
If you do need the duplicate rows in the final result, use UNION ALL with
your query. Otherwise you can opt for UNION as UNION is faster than UNION
ALL.
Thanks,
ViSolve DB Team.
----- Original Message -----
From: "PRASHANT N" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, August 01, 2006 5:28 PM
Subject: tune a geometric query
hi,
we are working on automotive traking solutions and insert our location
records into the mysql database v 4.1.20. If we want search for a
particular record, its taking too long time and mysql is identifying the
queries as slow queries. I have attached the queries.
How to optimize the attache 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
