Re: table index optimization
Hi Rick,
the table has got ~50k records.
Cheers,
Matus
<code>
CREATE TABLE `A_articles` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`subsection_select` int(11) unsigned NOT NULL DEFAULT '0',
`article_type` enum('article','summary','link') NOT NULL DEFAULT 'article',
`parent_article` int(11) unsigned NOT NULL DEFAULT '0',
`primary_channel` int(11) unsigned NOT NULL DEFAULT '0',
`primary_category` int(11) unsigned NOT NULL,
`R_headline` varchar(150) NOT NULL DEFAULT '',
`link_headline` varchar(100) NOT NULL DEFAULT '',
`summary` text NOT NULL,
`body_copy` text NOT NULL,
`external_url` varchar(255) NOT NULL DEFAULT '',
`source_magazine` enum('Pcpro','Computerbuyer','Computershopper','Macuser','Custompc','Itpro') NOT NULL DEFAULT 'Pcpro',
`on_listings` enum('yes','no') NOT NULL DEFAULT 'yes',
`valid_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`valid_to` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`author_email` varchar(255) NOT NULL,
`text_byline` varchar(50) NOT NULL DEFAULT '',
`photo_byline` varchar(50) NOT NULL DEFAULT '',
`template_select` int(11) unsigned NOT NULL DEFAULT '0',
`article_display_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`roundup_article` enum('yes','no') NOT NULL DEFAULT 'no',
`miva_keyword` varchar(20) NOT NULL DEFAULT '',
`webpage_title` varchar(255) NOT NULL DEFAULT '',
`meta_tags_description` text NOT NULL,
`meta_tags_keywords` varchar(255) NOT NULL DEFAULT '',
`canonical_link` varchar(200) NOT NULL,
`index_id` int(30) unsigned NOT NULL DEFAULT '0',
`root_id` int(11) unsigned NOT NULL DEFAULT '0',
`root_country` tinyint(3) unsigned NOT NULL DEFAULT '0',
`active` enum('yes','no') NOT NULL DEFAULT 'no',
`available` enum('yes','no') NOT NULL DEFAULT 'yes',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `R_headline` (`R_headline`),
KEY `valid_from` (`valid_from`),
KEY `valid_to` (`valid_to`),
KEY `active` (`active`),
KEY `article_type` (`article_type`),
KEY `available` (`available`),
KEY `root_id` (`root_id`),
KEY `root_country` (`root_country`),
KEY `index_id` (`index_id`),
KEY `parent_article` (`parent_article`),
KEY `roundup_article` (`roundup_article`),
KEY `subsection_select` (`subsection_select`),
KEY `source_magazine` (`source_magazine`),
KEY `article_display_date` (`article_display_date`),
KEY `on_listings` (`on_listings`),
KEY `primary_channel` (`primary_channel`)
) ENGINE=InnoDB AUTO_INCREMENT=374375 DEFAULT CHARSET=utf8 PACK_KEYS=0
</code>