MySQL supports spatial indexes exactly for circumstances like this:
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.ht...
Query optimization in MySQL with Subqueries
11–15 of 15 posts
Re: Query optimization in MySQL with Subqueries
#12Articles like this make me cry. Use a real database for god's sake (hint: it's free and the logo is an elephant).
Spoon-feeding trivial queries to MySQL is just wrong on so many levels.
Re: Query optimization in MySQL with Subqueries
#13I believe the first query was so slow because when using WHERE / IN - mysql will execute the subquery for each and every row, not do the subquery first as some might expect.
Re: Query optimization in MySQL with Subqueries
#14I'm reading the first query and maybe it's just early but why isn't that subquery just a join? It seems to be acting that way.
By definition all queries using JOINs can be re-written as sub-queries and vice versa. However, in some cases one is more intuitive, convenient or faster than the other. With MySQL you are better off with JOINs in most cases, and use subqueries only when there is a clear benefit and you understand why that would be faster. One exception to this is using a subquery with EXISTS, which is often way faster. While MySQL will tell you that it will look at all matching rows (when looking at EXPLAIN), it will only look at the first one it finds, so practically it ends up being faster.
Re: Query optimization in MySQL with Subqueries
#15Articles like this make me cry. Use a real database for god's sake (hint: it's free and the logo is an elephant). Spoon-feeding trivial queries to MySQL is just wrong on so many levels.
it's not an elephant, it's a dolphin...