MySQL Forums
Forum List  »  Optimizer & Parser

Please help me optimize this query
Posted by: Robert Kern
Date: July 28, 2008 05:23PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Please help me optimize this query
3111
July 28, 2008 05:23PM
2033
August 31, 2008 11:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.