Im stuck with a large table join, thats giving me max_join_size issues.
I have a table of properties (around 2500 entries), a table of towns & postcodes with Longitude & Latitude (38000 entries), and a table of images relating to the properties (42000 entries). The status table only has about 50 entries.
Im using google maps API to draw a circle on a map and fetching all properties within that circle, properties are only referenced with postcodes in my table, google maps uses GPS LatLng, hence the lookup table. I also need to pull one image for each prop from the images table and the current status
My sql with real data is this
SELECT prop_t.Prop_ID, prop_pics_t.Pic_ID, prop_t.Prop_Status_Code,
prop_t.Map_Pin_Postcode, prop_t.Map_Pin_Town, prop_t.Prop_Title_En,
prop_t.Area_Land, prop_t.Agents_Prop_Code, Publish
FROM prop_t
LEFT JOIN prop_pics_t ON prop_t.Prop_ID = prop_pics_t.Prop_ID
LEFT JOIN prop_stat_t ON prop_t.Prop_Status_Code = prop_stat_t.Property_Status_Code
JOIN (
SELECT postal_code FROM frenchmap
WHERE Lat BETWEEN '43.236572693911285' AND '47.54848605768498'
AND Lng BETWEEN '-0.7042013034011916' AND '5.437465803401324'
GROUP BY postal_code
) AS MAP ON MAP.postal_code = Map_Pin_Postcode
WHERE (Primary_Image = 'Y' OR Primary_Image IS NULL)
AND HQ_Image = 'N'
AND prop_t.Deleted = 'N'
AND Publish = 'Y'
AND Prop_Dept < '96'
AND Price_Euro BETWEEN '100000' AND '1000000'
AND Area_Land BETWEEN '0' AND '62000'
I can get it to work if I drop the image join, but that would mean Id need to access the DB again just to get the image reference, Id rather do it cleanly in one query.
Im assuming that Im attacking this wrongly in the sql
Yes visually its a circle but the coord’s returned form a rectangle, I may code the edges out later on if needed to return more of a circle.
Yes again its not exact and postcodes will overlap the circle edges, everyone knows their postcode, but not many their GPS location.
frenchmap as thats basically what the table provides for me, it holds all the info of french territories has a whole lot of other data in there too, polygons of town limits, postcodes, population details, official names of all towns and their administration statuses etc along with the gps latlng that Im using in this case … and it was the first name I came up with
[FONT=Courier New][FONT=Courier New]SELECT prop_t.Prop_ID, Images.Pic_ID, prop_t.Prop_Status_Code,
prop_t.Map_Pin_Postcode, prop_t.Map_Pin_Town, prop_t.Prop_Title_En,
prop_t.Area_Land, prop_t.Agents_Prop_Code, Publish
FROM prop_t
JOIN ([/FONT]
SELECT Pic_ID, Prop_ID
FROM prop_pics_t
WHERE Primary_Image = 'Y'
AND HQ_Image = 'N'
) AS Images ON prop_t.Prop_ID = Images.Prop_ID
JOIN (
SELECT postal_code
FROM frenchmap
WHERE Lat BETWEEN '43.236572693911285' AND '47.54848605768498'
AND Lng BETWEEN '-0.7042013034011916' AND '5.437465803401324'
GROUP BY postal_code
) AS MAP ON MAP.postal_code = Map_Pin_Postcode
LEFT JOIN prop_stat_t ON prop_t.Prop_Status_Code = prop_stat_t.Property_Status_Code
WHERE prop_t.Deleted = 'N'
AND Publish = 'Y'
AND Prop_Dept < '96'
AND Price_Euro BETWEEN '100000' AND '1000000'
AND Area_Land BETWEEN '0' AND '62000'
[/FONT]
which has made the error go away but is still slow (1.4 secs) despite indexing all the cols in the Joins … anything else I should be doing ?
Edit: the query is running faster today, though ive changed nothing except qualify the where clauses, its now giving me 0.6 secs which is more acceptable … I am using a shared host on this so suspect the timing is down to their server loads/issues etc
