Slow queries when using views compared to direct table queries

Hello,

Ive having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that Im seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one:

SELECT id, title, catid, created, tcpr_type FROM top_news_videos WHERE catid = '14' AND tcpr_type = '1' ORDER BY created DESC LIMIT 5

Running a page with 7 queries like that one (but changing the catid) takes an average of 4.50 seconds to load.

But if I run the queries directly to the original table named jos_content, the queries run a lot faster:

SELECT id, title, catid, created, tcpr_type FROM jos_content WHERE catid = '14' AND tcpr_type = '1' ORDER BY created DESC LIMIT 5

Running a page with 7 queries like that one (but changing the catid) takes an average of 0.30 seconds to load.

The reason I need to use views is because I have two tables. One is for text content and the other is for video content. But I display them both in several pages, so thats why I created the view with the union all. Here is the structure of these two tables:

[table jos_content]

CREATE TABLE `jos_content` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(100) collate latin1_general_ci NOT NULL default '',
`title_alias` varchar(100) collate latin1_general_ci NOT NULL default '',
`introtext` mediumtext collate latin1_general_ci NOT NULL,
`fulltext` mediumtext collate latin1_general_ci NOT NULL,
`state` tinyint(3) NOT NULL default '0',
`sectionid` int(11) unsigned NOT NULL default '0',
`mask` int(11) unsigned NOT NULL default '0',
`catid` int(11) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL default '0',
`created_by_alias` varchar(100) collate latin1_general_ci NOT NULL default '',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
`modified_by` int(11) unsigned NOT NULL default '0',
`checked_out` int(11) unsigned NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
`images` text collate latin1_general_ci NOT NULL,
`urls` text collate latin1_general_ci NOT NULL,
`attribs` text collate latin1_general_ci NOT NULL,
`version` int(11) unsigned NOT NULL default '1',
`parentid` int(11) unsigned NOT NULL default '0',
`ordering` int(11) NOT NULL default '0',
`metakey` text collate latin1_general_ci NOT NULL,
`metadesc` text collate latin1_general_ci NOT NULL,
`access` int(11) unsigned NOT NULL default '0',
`hits` int(11) unsigned NOT NULL default '0',
`exclusivo` int(1) NOT NULL default '0',
`wapaamerica` int(9) NOT NULL default '0',
`tcpr_type` int(1) unsigned NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `idx_section` (`sectionid`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`),
KEY `idx_state` (`state`),
KEY `idx_catid` (`catid`),
KEY `idx_mask` (`mask`),
KEY `state_access` (`state`,`access`,`publish_up`,`publish_down`,`id`,`catid`,`sectionid`,`created_by`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8864 ;

[table jos_xevgfx]

CREATE TABLE `jos_xevgfx` (
`id` int(11) NOT NULL auto_increment,
`catid` int(11) NOT NULL default '0',
`newsurl` int(11) NOT NULL default '0' COMMENT 'News URL for News Section',
`imgtitle` text collate latin1_general_ci NOT NULL,
`imgauthor` varchar(255) collate latin1_general_ci default NULL,
`imgtext` text collate latin1_general_ci NOT NULL,
`imgfulltext` text collate latin1_general_ci,
`imgdate` varchar(20) collate latin1_general_ci default NULL,
`frontpage` int(1) NOT NULL default '0',
`published_date` datetime default '0000-00-00 00:00:00',
`exp_date` datetime default '0000-00-00 00:00:00',
`imgcounter` int(11) NOT NULL default '0',
`imgvotes` int(11) NOT NULL default '0',
`imgvotesum` int(11) NOT NULL default '0',
`buylink` varchar(255) collate latin1_general_ci default NULL,
`buyactive` varchar(20) collate latin1_general_ci default NULL,
`otherlink` varchar(255) collate latin1_general_ci default NULL,
`otheractive` varchar(20) collate latin1_general_ci default NULL,
`published` tinyint(1) NOT NULL default '1',
`imgfilename` varchar(100) collate latin1_general_ci NOT NULL,
`imgthumbname` varchar(100) collate latin1_general_ci default NULL,
`movilfile` varchar(100) collate latin1_general_ci default NULL,
`file_id` varchar(100) collate latin1_general_ci default NULL,
`ordering` int(11) NOT NULL default '0',
`checked_out` int(11) NOT NULL default '0',
`owner` varchar(50) collate latin1_general_ci NOT NULL default '',
`author_by` varchar(100) collate latin1_general_ci default NULL,
`approved` int(1) NOT NULL default '0',
`useruploaded` int(1) NOT NULL default '0',
`exclusivo` int(1) NOT NULL default '0',
`wapamovil` int(9) NOT NULL default '0',
`tcpr_type` int(1) unsigned NOT NULL default '2',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5205;

Here is the SQL I used to create the view and making the union between these tables.

CREATE VIEW top_news_videos AS SELECT id, title, catid, created, introtext AS intro_text, `fulltext` AS full_text, images AS image, created_by_alias AS author, tcpr_type, exclusivo
FROM jos_content, jos_content_frontpage
WHERE jos_content. state =1
AND jos_content.id = jos_content_frontpage.content_id
AND (publish_up <= DATE_ADD(NOW(), INTERVAL 01 HOUR)
OR publish_up = '0000-00-00 00:00:00')
AND (publish_down >= DATE_ADD(NOW(), INTERVAL 01 HOUR)
OR publish_down = '0000-00-00 00:00:00')
UNION ALL
SELECT id, imgtitle AS title, catid, published_date AS created, imgtext AS intro_text, imgfulltext AS full_text, imgfilename AS image, author_by AS author, tcpr_type, exclusivo
FROM jos_xevgfx
WHERE frontpage = '1' AND newsurl = '0' AND tcpr_type = '2'
AND (published_date <= DATE_ADD(NOW(), INTERVAL 01 HOUR)
OR published_date = '0000-00-00 00:00:00')
AND (exp_date >= DATE_ADD(NOW(), INTERVAL 01 HOUR)
OR exp_date = '0000-00-00 00:00:00')

When I export the view (using phpmyadmin) here is the export script I get:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `top_news_videos` AS select sql_no_cache `jos_content`.`id` AS `id`,`jos_content`.`title` AS `title`,`jos_content`.`catid` AS `catid`,`jos_content`.`created` AS `created`,`jos_content`.`introtext` AS `intro_text`,`jos_content`.`fulltext` AS `full_text`,`jos_content`.`images` AS `image`,`jos_content`.`created_by_alias` AS `author`,`jos_content`.`tcpr_type` AS `tcpr_type`,`jos_content`.`exclusivo` AS `exclusivo` from (`jos_content` join `jos_content_frontpage`) where ((`jos_content`.`state` = 1) and (`jos_content`.`id` = `jos_content_frontpage`.`content_id`) and ((`jos_content`.`publish_up` <= (now() + interval 1 hour)) or (`jos_content`.`publish_up` = _utf8'0000-00-00 00:00:00')) and ((`jos_content`.`publish_down` >= (now() + interval 1 hour)) or (`jos_content`.`publish_down` = _utf8'0000-00-00 00:00:00'))) union all select `jos_xevgfx`.`id` AS `id`,`jos_xevgfx`.`imgtitle` AS `title`,`jos_xevgfx`.`catid` AS `catid`,`jos_xevgfx`.`published_date` AS `created`,`jos_xevgfx`.`imgtext` AS `intro_text`,`jos_xevgfx`.`imgfulltext` AS `full_text`,`jos_xevgfx`.`imgfilename` AS `image`,`jos_xevgfx`.`author_by` AS `author`,`jos_xevgfx`.`tcpr_type` AS `tcpr_type`,`jos_xevgfx`.`exclusivo` AS `exclusivo` from `jos_xevgfx` where ((`jos_xevgfx`.`frontpage` = _utf8'1') and (`jos_xevgfx`.`newsurl` = _utf8'0') and (`jos_xevgfx`.`tcpr_type` = _utf8'2') and ((`jos_xevgfx`.`published_date` <= (now() + interval 1 hour)) or (`jos_xevgfx`.`published_date` = _utf8'0000-00-00 00:00:00')) and ((`jos_xevgfx`.`exp_date` >= (now() + interval 1 hour)) or (`jos_xevgfx`.`exp_date` = _utf8'0000-00-00 00:00:00')));

What is suggested to do in order to have a better performance in my queries for these two tables? Is there anything I can change in the view o something else that can be done?

Thanks!Start Free Trial