A couple of thoughts:
1) How big are the tables?
2) Is vhs indexed on ID?
3) Is performersRel indexed on performerID?
4) If you don't need all the fields in VHS, specify them.
5) Is studio indexed on ID?
6) How fast does the subquery run by itself?
-----Original Message-----
From: Stuart Duncan [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 17, 2000 3:17 PM
To: CF-Talk
Subject: Speeding up a multi table query
I was wondering if anyone knows of a way to speed up this query? If "Joins"
would be better, and how? That sorta thing.
The actors, studios and prices are all in separate tables. "vhs" "pricing"
"studios" and "performersRel"
There has to be a faster way to get 22 records back.
resultsbyperformer (Records=22, Time=328ms)
SQL =
select vhs.*, p.price, s.studio, s.ID as sID, s.studioID
from vhs,pricing p,studios s
where p.priceID = vhs.price
and s.ID = vhs.studio
and vhs.ID in (select videoID from performersRel where performerID = 1627)
Any help would be greatly appreciated.
Stuart Duncan
MaracasMedia Inc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
