SQL Query for Wards/Postcodes Taking Too LongI don't quite understand why this query
select distinct left(z.pc,4) from tblPostcodes as z where z.ward in (select distinct y.ward from tblPostcodes as y where y.pc like 'NW10%')has been running for over 48 hours and still is "Copying to tmp table", while the internal query
select distinct y.ward from tblPostcodes as y where y.pc like 'NW10%'returns 14 lines within 2--3 seconds, and subsequently the query which substitutes the internal query with its result
select distinct left(z.pc,4) from tblPostcodes as z where z.ward in ('E05000089', 'E05000100', 'E05000103', 'E05000102', 'E05000091', 'E05000104', 'E05000105', 'E05000087', 'E05000092', 'E05000098', 'E05000253', 'E05000175', 'E05000179', 'E05000085')again returns the result (12 lines) within 2 seconds.
The total number of lines in tblPostcodes is around 1.7 million, the number of rows matching "NW10%" is about 1800, and the number of rows within the 14 wards is approximately 4000.