Problem with fairly complex query

[Dave]

Hi all,

I have a problem with a query. (well, I actually have a few problems...)

Here is my query.

select FOCUS.SiteName, FOCUS.URL, OTWAdCars.* , REGION.SiteName as
RegionSite, REGION.URL as RegionUrl
from OTWSite as FOCUS
right join OTWSite as REGION
on (((REGION.Eastings >= FOCUS.Eastings - 10)
and (REGION.Eastings <= FOCUS.Eastings + 10))
and ((REGION.Northings >= FOCUS.Northings - 10)
and (REGION.Northings <= FOCUS.Northings + 10)))
right join OTWAdCars
on SiteID = REGION.ID
and ((OTWAdCars.adLocation >= 2)
or (OTWAdCars.SiteID = 1 and OTWAdCars.adLocation >= 4))
where
(FOCUS.ID = 1 or External in (0, 602944126930, 288748667206, 854969239454,
832485371888, 418635492275, 23671466569, 482286983973, 301430737751,
796819020180, 867063330985, 843854571461, 659585236304, 829099029074,
272879962942, 620381345525, 252030523924, 143574956414, 298334166197,
884781346283, 333547616524, 400867572135, 368764497317, 194954968838,
863869288183, 700810190421, 415692000349, 369433922812, 522425442804,
50352719477, 548970429743, 450125473881, 853234396932, 617546273889))
and approved = 1
and date_format(dateofexpiry, '%Y%m%d') >= date_format(now(), '%Y%m%d')
order by ExSort Desc, OTWAdCars.id desc
Let me describe what I have.

I have two tables, OTWSite and OTWAdCars.
In order to get my region, I am doing a 1 to many relationship from OTWSite
(as FOCUS) to OTWSite (as REGION) then hoping to find all items in OTWAdCars
within the region (if defined as regional items.)

However...

to add to the confusion, some (in fact, at the moment, ALL) the records are
not listed with a OTWSite.ID number (though there will be mixed) so I need
to get the items in the list of numbers (in the query, which is generated
from an earlier query). Because this is "external" data, I have an external
identifier in OTWAdCars.

I currently have about 1000 records. The query above is returning approx
3000 and taking 1.5 minutes to run (way too long).

I had shuffled it around and got it down to a more reasonable number and
much faster but
1. still too many, the result was a multiple of the numbers in brackets
times how many regions)
2. before I could save the query, my frontend to MySQL crashed.

(There are 33 items in the numbered list. Ignore 0. That is there just in
case I don't have any items for my list so that the query doesn't stop)
What I am trying to achieve.

The query above should currently return me:
1. any items in the OTWAdCars table where external in the list of numbers
2. any items in the OTWAdCars table where the item is within the region.

I don't want to have multiple rows of the same item with different FOCUS
sitenames.

Sorry it has taken long to describe. It has had me stumped for days now.

I know the region part works OK. Maybe not too efficient but it does work.

Thanks for listening. If there is any info I have missed, please let me know
and I will try and provide it.

Best regards,
Dave Colliver.
http://www.FOCUSPortals.com
--
~~
All I ask is a chance to prove that money can't make me happy.

Your community connected. http://www.ashfieldfocus.com
Get your business on the net, http://www.clickprovince.com
Collectible dolls from http://www.collectorsdolls.com
Web design, hosting, domain registration? http://www.revilloc.com is the
answer.
Tips, tricks and articles for programmers of all languages on
http://www.sourcecodecorner.com
Planning a wedding? http://www.bmcweddings.com
Advertise your car on http://www.drivingseat.com