I am working on a news site, the table (news) contains about 200,000 rows. I use the Zend 1. structure. The site worked very well, but a week ago I found some errors in the data received from the requests.
I am using zend paginator, for example:
$paginator = Zend_Paginator::factory($select);
$paginator->setItemCountPerPage(10);
$pageCounter = $paginator->count();
so $ pageCounter returns 0,
when I try to debug the error, I tried to remove the indexes from the database table and create the index again, after which the problem was solved again in order to get back to work. but every couple of days this problem returns again. The tables I use are:
CREATE TABLE `news` (
`news_id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`news_type` enum('text','photo','video') DEFAULT 'text',
`news_title` varchar(255) NOT NULL,
`news_url` varchar(255) NOT NULL,
`news_date` datetime NOT NULL,
`news_image` varchar(255) DEFAULT NULL,
`video` varchar(255) DEFAULT NULL,
`news_source` int(11) NOT NULL DEFAULT '0',
`author_id` int(11) NOT NULL DEFAULT '0',
`news_brief` text,
`news_content` mediumtext,
`meta_title` varchar(255) NOT NULL,
`meta_description` varchar(255) DEFAULT NULL,
`meta_keywords` varchar(255) DEFAULT NULL,
`status` tinyint(2) NOT NULL DEFAULT '1',
`news_read` int(11) DEFAULT '0',
`old_id` int(11) DEFAULT NULL,
`old_section_id` int(11) DEFAULT NULL,
`old_section` varchar(50) DEFAULT NULL,
`lang` varchar(10) DEFAULT NULL,
`featured` int(11) NOT NULL DEFAULT '0',
`in_timeline` tinyint(2) NOT NULL DEFAULT '0',
`is_highlight` tinyint(2) NOT NULL DEFAULT '0',
`home_exclusive` tinyint(2) NOT NULL DEFAULT '0',
`home_articles` tinyint(4) NOT NULL DEFAULT '0',
`home_articles_selected` tinyint(4) NOT NULL DEFAULT '0',
`home_mostread` tinyint(4) NOT NULL DEFAULT '0',
`video_featured` tinyint(4) NOT NULL DEFAULT '0',
`photos_featured` tinyint(4) NOT NULL DEFAULT '0',
`party_featured` tinyint(4) NOT NULL DEFAULT '0',
`party_activity` tinyint(4) NOT NULL DEFAULT '0',
`sport_featured` tinyint(4) NOT NULL DEFAULT '0',
`fnoun_featured` tinyint(4) NOT NULL DEFAULT '0',
`special_reports` tinyint(4) NOT NULL DEFAULT '0',
`mainnav` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`news_id`),
KEY `News_Category_idx` (`category_id`),
KEY `News_Type` (`news_type`),
KEY `News_Date` (`news_date`),
KEY `News_Read` (`news_read`),
KEY `old_id` (`old_id`),
KEY `Sources` (`news_source`),
KEY `Authors` (`author_id`),
KEY `Featured` (`featured`),
KEY `Articles` (`home_articles`),
KEY `Mostread` (`home_mostread`),
KEY `video_featured` (`video_featured`,`photos_featured`),
KEY `party_activity` (`party_activity`),
KEY `sport_featured` (`sport_featured`,`fnoun_featured`),
KEY `old_section_id` (`old_section_id`),
KEY `old_section` (`old_section`),
KEY `old section id` (`old_section_id`),
KEY `old section` (`old_section`),
KEY `articles_selected` (`home_articles_selected`),
KEY `URL` (`news_url`),
KEY `special_reports` (`special_reports`),
KEY `mainnav` (`mainnav`),
KEY `status` (`status`),
KEY `in_timeline` (`in_timeline`),
KEY `is_highlight` (`is_highlight`),
KEY `home_exclusive` (`home_exclusive`),
KEY `party_featured` (`party_featured`),
FULLTEXT KEY `news_title` (`news_title`)
) ENGINE=InnoDB AUTO_INCREMENT=167614 DEFAULT CHARSET=utf8
(status - featured - in_timeline - is_highlight) (ENUM), (tinyint)
, .
.
, ?
- , .
, , , , .
SQL query: Explain SELECT `N`.`news_id`, `N`.`news_title`, `N`.`news_url`, `N`.`news_image`, `N`.`news_type`, `N`.`news_date`, `N`.`is_highlight`, `N`.`news_brief`, `C`.`category_name`, `C`.`category_url`, `C`.`category_color`, `NT`.*, GROUP_CONCAT(T.tag_name separator ",") AS tags, GROUP_CONCAT(T.tag_url separator ",") AS `tags_urls` FROM `news` AS `N` INNER JOIN `news_categories` AS `C` ON C.category_id = N.category_id AND C.status = "1" LEFT JOIN `news_tags_relation` AS `NT` ON NT.news_id = N.news_id LEFT JOIN `news_tags` AS `T` ON T.tag_id = NT.tag_id WHERE (N.status = "1" AND N.category_id = "22") GROUP BY `N`.`news_id` ORDER BY `N`.`news_date` DESC;
: 4
This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
