Please help me optimize this query
Hi there,
Firstly i thank you in advance for any help with this. It is a database with about 300,000 records of DVD/Film etc publication details for a labelling organisation.
I will copy below the structure of the tables in question, and a copy of the query i am trying to optimise. The query gives me the exact results i am looking for except that it runs too slowly... way too slowly. It runs faster without the GROUP BY at the end, but even then its pretty slow.
First, the tables:
--
-- Table structure for table `fvlb_publications`
--
CREATE TABLE `fvlb_publications` (
`id` int(11) NOT NULL auto_increment,
`fvlb_id` int(11) NOT NULL default '0',
`title_id` int(11) NOT NULL default '0',
`media_id` int(11) NOT NULL default '0',
`rating_id` int(11) NOT NULL default '0',
`display_conditions` tinyint(1) NOT NULL default '0',
`release_date` datetime NOT NULL default '0000-00-00 00:00:00',
`approved_date` datetime NOT NULL default '0000-00-00 00:00:00',
`distributor_id` int(11) NOT NULL default '0',
`alert_id` int(11) NOT NULL default '0',
`cut_id` int(11) NOT NULL default '0',
`ezSearchLongName` varchar(255) NOT NULL default '',
`ezSearchAkaName` varchar(255) NOT NULL default '',
`ezSearchTranslatedName` varchar(255) NOT NULL default '',
`ezCreatedDate` datetime NOT NULL default '0000-00-00 00:00:00',
`ezLastModifiedDate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `fvlb_id` (`fvlb_id`),
KEY `rating_id` (`rating_id`),
KEY `media_id` (`media_id`),
KEY `title_id` (`title_id`),
KEY `approved_date` (`approved_date`),
KEY `ezSearchLongName` (`ezSearchLongName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=186344 ;
-- --------------------------------------------------------
--
-- Table structure for table `fvlb_titles`
--
CREATE TABLE `fvlb_titles` (
`id` int(11) NOT NULL auto_increment,
`fvlb_id` int(11) NOT NULL default '0',
`long_name` varchar(255) NOT NULL default '',
`trans_name` varchar(255) NOT NULL default '',
`aka_name` varchar(255) NOT NULL default '',
`short_name` varchar(45) NOT NULL default '',
`index_name` varchar(255) NOT NULL default '',
`ezSearchLongName` varchar(255) NOT NULL default '',
`ezSearchAkaName` varchar(255) NOT NULL default '',
`ezSearchTranslatedName` varchar(255) NOT NULL default '',
`cast_members` text NOT NULL,
`director` varchar(50) NOT NULL default '',
`ezCreatedDate` datetime NOT NULL default '0000-00-00 00:00:00',
`ezLastModifiedDate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `fvlb_id` (`fvlb_id`),
KEY `director` (`director`),
KEY `ezLastModifiedDate` (`ezLastModifiedDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=130082 ;
Now the query:
SELECT fvlb_titles.fvlb_id AS title_id,
fvlb_titles.long_name,
fvlb_titles.cast_members,
fvlb_titles.director,
fvlb_publications.fvlb_id AS pub_id,
fvlb_publications.release_date AS released,
fvlb_publications.approved_date
FROM fvlb_publications
LEFT OUTER JOIN fvlb_titles ON fvlb_publications.title_id = fvlb_titles.fvlb_id
WHERE (fvlb_publications.approved_date >= '1994-10-01 00:00:00'
AND fvlb_publications.approved_date <= '2008-07-30 00:00:00')
AND fvlb_publications.media_id != '6'
AND fvlb_publications.media_id != '1016509723'
AND fvlb_publications.media_id != '1081138071'
AND fvlb_publications.media_id != '1127869002'
AND fvlb_publications.rating_id != '213'
AND (fvlb_publications.ezSearchLongName LIKE '%Jumper%' OR fvlb_publications.ezSearchTranslatedName LIKE '%Jumper%' OR fvlb_publications.ezSearchAkaName LIKE '%Jumper%')
GROUP BY fvlb_publications.title_id
ORDER BY fvlb_publications.approved_date DESC
LIMIT 0,60
When I EXPLAIN the query as it is above it tells me it will use where, temporary and filesort. If i remove the GROUP BY line it only uses where - but is still fairly slow.
Any advice would be much appreciated.