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
Re: tune a geometric query
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. You've posted this multiple times, that's just going to annoy everyone. Break the query down and find out which part is slow. Each union part should work by itself, so take each one and tune that. Then worry about the whole lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tune a geometric query
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
Re: tune a geometric query
On Tue, 2006-08-01 at 17:39 +0530, ViSolve DB Team wrote: 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. I have never heard of any evidence of this; in fact, it makes more sense that the reverse would be true, as MySQL would not have to do an implicit DISTINCT on the outermost resultset. Prashant: Please post an EXPLAIN of your original posted SQL query. Use the /G flag from the command line client to make it easier to read the results. Thanks! -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
