0

I have this query:

    SELECT id,type,source_id,link,short_link,cat_id,title,description,images,views,comments,published,publishedon FROM listings WHERE 
(YEARWEEK( FROM_UNIXTIME( publishedon ) , 1 ) = YEARWEEK( CURDATE( ) , 1 )) AND 
(id != 88587) AND 
(cat_id = '64') 
AND (deleted = 0)
AND (published = 1)
ORDER BY views DESC LIMIT 1 OFFSET 0

table explain enter image description here enter image description here

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `hash` char(32) NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `link` varchar(255) NOT NULL,
  `short_link` varchar(255) NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `description` mediumtext,
  `content` mediumtext,
  `images` mediumtext,
  `videos` mediumtext,
  `views` int(10) unsigned NOT NULL,
  `comments` int(11) DEFAULT '0',
  `comments_update` int(11) NOT NULL DEFAULT '0',
  `editor_id` int(11) NOT NULL DEFAULT '0',
  `auther_name` varchar(255) DEFAULT NULL,
  `createdby_id` int(10) NOT NULL,
  `createdon` int(20) NOT NULL,
  `editedby_id` int(10) NOT NULL,
  `editedon` int(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `deletedon` int(20) NOT NULL,
  `deletedby_id` int(10) NOT NULL,
  `deletedfor` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `publishedon` int(20) NOT NULL,
  `publishedby_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8956 ;

I cannot make any changes in fields types.

Is there any way to make it fast? It takes to execute 0.7518 sec,0.8776 sec.

  • 1
    Table definition would help together with the EXPLAIN – Mihai Sep 29 '15 at 12:21
  • 2
    Query EXPLAIN please (that shows the expected execution plan). Better to add the CREATE TABLE statement instead of that image. – jkavalik Sep 29 '15 at 12:28
  • Also, what would you consider fast? Half a second? 0.1s? Less? – dezso Sep 29 '15 at 12:31
  • added, i have 5 or 6 queries like this in one page , its very hard, i want it smaller as possible – Qlkady Sep 29 '15 at 12:57
  • 1
    And we (and YOU) definitely need results of EXPLAIN used on the query you want to optimize. Thats a special function which shows HOW the query is executed and some estimates of rows "visited". – jkavalik Sep 29 '15 at 13:19
0

This should help significantly (unless everything is cat 64):

INDEX(cat_id, deleted, published)

If 'they' won't add that index (or let you add it), warm up your resume.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.