help speeding up a query

I have a query that works correctly, but is very slow to execute.  Can anyone suggest a faster way to write it, or deal with it?  I'm also using coldfusion for the logic portion, just so you know.

The `jobsworked` and `timein` tables store staff time entries per job.  The job info is stored in the `jobitems` table, and staff info in the `staff` table.  Staff billing rates are in the `billingrates` table.  The Job Cost is calculated by multiplying the hours worked on a job by the rate that was in effect at the time the hours were worked (timein.dateworked column, as well as billingrates.validasof).  As I mentioned, the query below works perfectly, but it is slow.  I'm trying to find a way to re-write it, or better index my tables, to speed it up.

I'll list the query here, and below the source code for all the tables:

select jobsworked.jobitemsid,jobcostlink,namedisplay,jobsworked.staffid,hours,dateworked,
                              (SELECT rate FROM billingrates WHERE staffid=staff.staffid AND validasof <= timein.dateworked
                                    ORDER BY validasof DESC LIMIT 1) AS rate,
                              (SELECT validasof FROM billingrates      WHERE staffid=staff.staffid AND validasof <= timein.dateworked
                                    ORDER BY validasof DESC      LIMIT 1) AS validasof
                              from timein
                              inner join jobsworked using(jobsworkedid)
                              inner join staff on staff.staffid = jobsworked.staffid
                              inner join jobitems on jobitems.jobitemsid = jobsworked.jobitemsid
                              where jobsworked.jobitemsid in (#list_of_jobitemsids#) and hours > 0
                              order by namedisplay

Tables:
CREATE TABLE `jobsworked` (
  `JobsWorkedID` int(10) unsigned NOT NULL auto_increment,
  `StaffID` int(10) NOT NULL default '0',
  `JobItemsID` int(10) NOT NULL default '0',
  `WorkDescription` text NOT NULL,
  `ForWeekStarting` date default NULL,
  PRIMARY KEY  (`JobsWorkedID`),
  KEY `jwid_jiid_idx` (`JobsWorkedID`,`JobItemsID`)
) ENGINE=MyISAM AUTO_INCREMENT=200698 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `timein` (
  `TimeInID` int(10) unsigned NOT NULL auto_increment,
  `DateWorked` date NOT NULL default '0000-00-00',
  `JobsWorkedID` int(10) NOT NULL default '0',
  `Hours` float(10,2) NOT NULL default '0.00',
  `Pushed` int(1) NOT NULL default '0',
  PRIMARY KEY  (`TimeInID`),
  KEY `jwid_hours_timein_idx` (`JobsWorkedID`,`Hours`)
) ENGINE=MyISAM AUTO_INCREMENT=1213784 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

CREATE TABLE `staff` (
  `StaffID` int(5) NOT NULL auto_increment,
  `FirstName` varchar(50) default NULL,
  `Middle` varchar(50) NOT NULL default '',
  `LastName` varchar(50) default NULL,
  `Dept` varchar(5) NOT NULL default '',
  `SubDivision` varchar(25) NOT NULL default '',
  `NameDisplay` varchar(50) default NULL
  PRIMARY KEY  (`StaffID`),
  UNIQUE KEY `StaffID` (`StaffID`,`Status`,`NameDisplay`)
) ENGINE=MyISAM AUTO_INCREMENT=408 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `jobitems` (
  `JobItemsID` int(10) NOT NULL auto_increment,
  `Description` text NOT NULL,
  PRIMARY KEY  (`JobItemsID`),
  KEY `jiid_ciid_idx` (`JobItemsID`,`CompanyInfoID`),
  KEY `jiid_jobitems_idx` (`JobItemsID`,`JobsID`,`Status`)
) ENGINE=MyISAM AUTO_INCREMENT=52619 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `billingrates` (
  `BillingRateID` int(10) NOT NULL auto_increment,
  `staffid` int(10) default NULL,
  `name` varchar(255) default NULL,
  `rate` smallint(5) default NULL,
  `validasof` date default NULL,
  PRIMARY KEY  (`BillingRateID`),
  KEY `staffid` (`staffid`,`rate`)
) ENGINE=MyISAM AUTO_INCREMENT=601 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Start Free Trial