List:General Discussion« Previous MessageNext Message »
From:Matt Neimeyer Date:June 22 2009 3:01pm
Subject:Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
View as plain text  
On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisley<brenttech@stripped> wrote:
> It sounds like you want to use spatial indexes, but they only became
> available in v4.1
> http://dev.mysql.com/doc/refman/5.0/en/create-index.html
> http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html

That "feels" like the right thing (spatial calculations = spatial
indexes?) but I looked at the docs and my head exploded. Can anyone
recommend a good book that takes me through it gently?

That said I'm intreged by the MBRContains and the Polygon functions...
If I read those right I could create a simplified "circle" (probably
just an octogon) to help eliminate false positives in the "corners"
when using a plain square as the enclosure.

> You don't have to do any re-architecture to change you subquery to a join:
> SELECT custzip FROM customers
> JOIN
> (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
>
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude))))*60*1.1515
> < 5) AS zips
> ON custzip=zip

Will that work after a where clause? Multiple Times? For example...
(pseudo-code...)

SELECT * FROM customers WHERE saleslastyear > 100000
   JOIN (SELECT zip FROM etc....) AS zips ON custzip=zip
   JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid

Just from thinking about that... I assume that the only limitation is
that in a subselect you can do something like WHERE NOT IN (select
etc) but with a JOIN you are assuming a "positive" relationship? For
example using the JOIN methods above there isn't a way to simply do
"AND custid NOT IN (SELECT custid FROM ordersplacedthisyear)" other
than doing exactly that and adding this clause to the saleslastyear
clause. (In this particular case a column "lastorderdate" in customer
that was programatically updated on ordering would also be useful but
I'm thinking examples here... ;) )

I've never seen JOIN used outside of a traditional "SELECT t1.*,t2.*
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id" type of
structure so I kinda feel like I have a new toy...

Thanks!
Thread
Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Johnny Withers19 Jun
    • How to Optimize distinct with index周彦伟19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Dan Nelson19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer19 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Brent Baisley19 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Matt Neimeyer22 Jun
  • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Peter Brawley20 Jun
    • Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?Walter Heck - OlinData.com20 Jun
Re: How to Optimize distinct with indexDan Nelson19 Jun
Re: How to Optimize distinct with indexDarryle Steplight19 Jun
  • Re: How to Optimize distinct with indexMoon's Father26 Jun