CODEWITHSUNDEEP
mysql
bar-boss
bar-boss

Reputation: 23

Mysql "Sending data" too long

I have table com_news with 32000 records.

The table com_news

CREATE TABLE IF NOT EXISTS `com_news` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ts` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`text` longtext NOT NULL,
`id_user` int(11) NOT NULL DEFAULT '0',
`id_catalog` int(11) NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0-off,1-on',
`window_title` varchar(255) DEFAULT NULL,
`meta_keywords` varchar(255) DEFAULT NULL,
`meta_description` text,
`source` varchar(255) DEFAULT NULL,
`img` int(11) NOT NULL DEFAULT '0',
`to_twitter` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=32719 ;

The query

SELECT COUNT(*) FROM `com_news` WHERE status=1 AND ts>=1398888007

Profile

starting    0.000009
checking query cache for query  0.000027
Opening tables  0.000014
System lock 0.000003
Table lock  0.000016
init    0.000015
optimizing  0.000009
statistics  0.000009
preparing   0.000008
executing   0.000004
Sending data    0.517773
end 0.000013
query end   0.000003
freeing items   0.000025
storing result in query cache   0.000006
logging slow query  0.000002
cleaning up 0.000003

Why is Sending data is too long?

Apache/2.2.15 (CentOS)
MySQL: 5.1.73

Upvotes: 0

Views: 2866

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You are missing index in the table.

So here what you can do to increase the performance

alter table com_news add index stat_ts_idx (status,ts);

Now make sure you take a backup of the table and data before applying the index.

Also you should always check the explain plan for the query which will tell you a lot.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Upvotes: 1

Related Questions