Most Recent Comments

2018-02-10 14:03:13   

z

Calling OpenSchema Method To Find Out Database Name

zz


RSS

SQL Query for Wards/Postcodes Taking Too Long

I 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.


Comments

No comments.

Your Name:
Your Comment:

5 minus 1 is: