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
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
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
Thanks for any pointers