expensive query in need of a re-write

[galorin]

I have a query that references multiple views (Bad I know, but my sequel is not strong)

Main tables referenced:

HoursWorked : Log of hours spent designing each HouseType.
HouseType : Designs connected to each quotation, has assigned hours as well.
quotations : Should be called Sites, contain site address details, etc.Connected to Companies via both distributor and builder.
people : names and contacts for people within company
companies : Companies, contains company contact details.

Next, the views that are referenced: All are ugly suckers, but I don't know how else to do it. I wasn't responsible for the old database design, but I am responsible for the new database and migrating data, so hopefully I can do better.

BuyingContact view:

SELECT p.per_id as bCon, q.quote_id FROM people as p
left join quotations as q on q.buying_contact = concat(p.`First Name`, " ", p.`Last Name`)
where q.quote_id is not null
and p.`First Name` <> ''

DesignContact view: (references commented out)

SELECT p.per_id as dCon, q.quote_id FROM people as p
left join quotations as q on q.design_contact = concat(p.`First Name`, " ", p.`Last Name`)
where q.quote_id is not null
and p.`First Name` <> ''

RepsForComps view: (view works, not sure about implementation in this query, business logic not hammered out yet)

select c.Comp_ID
, concat(j.`First Name`," ",j.`Last Name`) AS name
from `jji contacts` AS j
left join counties AS o on j.`JJI Contact` = o.`Assigned Rep`
left join companies AS c on c.County = o.County
where c.Company <> ''
and c.`Rep Override` <> ''
union
select c.Comp_ID
,concat(j.`First Name`, " ", j.`Last Name`) AS name
from companies as c
left join `jji contacts` AS j on j.`JJI Contact` = c.`Rep Override`
where length(c.`Rep Override`) = 2 or c.`Rep Override` = 'TRBJ'
union
select c.Comp_ID
, c.`Rep Override`
from companies as c
where length(c.`Rep Override`) > 4

Ok, now that I've laid out some of the foundations, here is my time-consuming query:

SELECT q.quote_id
, q.glulamValue
, q.date_returned
, q.distributor
, concat(a.`First Name`, " ", a.`Last Name`) as buying_contact
, a.`Direct Dial` as buyCon_tel
, q.builder
[COLOR=green]-- , concat(b.`First Name`, " ", b.`Last Name`) as design_contact [/color]
[COLOR=green]-- , b.`Direct Dial` as desCon_tel[/color]
[COLOR=green]-- , r.name[/color]
, h.design_production_no, h.designer, h.Hours, h.house_type
, sum(w.`Hours Worked`) as worked
from quotations as q
left join HouseType as h using (Quote_ID)
left join hoursworked as w using (ht_id)
left join BuyingContact as c using (Quote_ID)
[COLOR=green]-- left join DesignContact as c using (Quote_ID)[/color]
left join people as a on c.bcon = a.Per_ID
[COLOR=green]-- left join RepsForComps as r on r.company = q.distributor or r.company = q.builder[/color]
where q.date_returned > '2003-01-01' [COLOR=green]-- this may become more restrictive, if I can pull it off at the application level[/color]
and h.design_production_no is not null
and h.design_production_no not like ''
group by h.house_type

As it is written, the query takes 7 minutes on our hardware. I know this isn't a lot of time, but I still have heaps more to add to the query. I also know significant speed savings will be mine if I can restrict on q.date_returned at the application level, but as it's a MS Access report, I don't know. I want to know if there are any techniques that I could employ to make this query faster/easier/better or if it is destined to be the "Go on a tea break" query.

I have done "explain extended", put in appropriate indexes, and optimised the server as best as I know how. If those who have strong MySQL can pick out my flaws, that would be appreciated as well.