Optimize MySQL order by query

Question

I have the following query to get the most viewed articles that submitted in last month for example.

explain 
SELECT * 
FROM article 
WHERE date > 1315391769 
ORDER BY views DESC 
LIMIT 10

how do I choose the right index for this query? or how do I re-write it to avoid scanning a lot of rows or file sorting?

This is the table scheme with the current indexes I tried:

CREATE TABLE `article` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `body` text NOT NULL,
  `date` int(32) NOT NULL,
  `views` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `date` (`date`),
  KEY `views` (`views`),
  KEY `date_2` (`date`,`views`),
  KEY `views_2` (`views`,`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=200003 ;

-- 
-- Dumping data for table `article`
-- 

INSERT INTO `article` VALUES (1, 'title test113', 'test body118', 1317912183, 5017);
INSERT INTO `article` VALUES (2, 'title test193', 'test body193', 1313441124, 5943);
INSERT INTO `article` VALUES (3, 'title test112', 'test body116', 1312773586, 653);
INSERT INTO `article` VALUES (4, 'title test378', 'test body374', 1316786646, 4589);
INSERT INTO `article` VALUES (5, 'title test335', 'test body3310', 1319173694, 6224);

Notice: I also tried mysql date instead of Unix timestamp but I got the same result.

This is the output of EXPLAIN:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  article     range   date,date_2     date    4   NULL    107245  Using where; Using filesort

what is an int(32) ? is it some kinda signed bigger int ? and for that matter, what is an int(11) ?? dev.mysql.com/doc/refman/5.0/en/numeric-types.html — Jon Black
– Jon Black, Commented Oct 27, 2011 at 23:48
It should be 11 not 32. I already change it to MySQL time and MySQL datetime and I got the same result. — usef_ksa
– usef_ksa, Commented Oct 28, 2011 at 0:01
EXPLAIN is rather useless when the table has so few rows. Try having a few thousands or tens of thousands. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Oct 28, 2011 at 0:18

ypercubeᵀᴹ · Accepted Answer · 2011-10-28 00:37:16Z

1

For this kind of queries, only either (date) or (views) index can be used and not the compound (date,views). What MySQL chooses to use may or may not be optimal. And what is optimal for February may not be for April data!

You can try forcing one of the two and measure preformance.
You could add a calculated column with Year-Month data, using a CHAR(6) like 201104 for April 2011 (that column can be updated using INSERT and UPDATE triggers) or an int like 24136 (24136 = 2011*12 + 4).

Then your condition would be:

WHERE YearMonth = '201109'            --- for September

or

WHERE YearMonth = 2011*12+9

and a (YearMonth, views) index could be used.

answered Oct 28, 2011 at 0:37

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sowmiya · Accepted Answer · 2012-01-06 14:36:43Z

1

By forcing index, it is achieved.

explain 
SELECT * 
FROM article force index (`views`)
WHERE date>=1315391768  
ORDER BY views DESC 
LIMIT 10

Explain plan:

"id"    "select_type"   "table"     "type"       "possible_keys"    "key"   "key_len"     "ref" "rows"  "Extra"
 "1"    "SIMPLE"       "article"    "index"           \N           "views"    "4"           \N  "5" "Using where"

answered Jan 6, 2012 at 14:36

Sowmiya

3132 silver badges13 bronze badges

Comments

Aditya Naidu · Accepted Answer · 2012-01-28 16:09:38Z

1

A single key date_2 (date,views) should give optimal performance for the query in question. See here I don't think the other indexes are going to help. Since the query is so simple I can't think of any other optimization!

edited Jan 28, 2012 at 16:09

answered Oct 28, 2011 at 0:16

Aditya Naidu

1,3709 silver badges12 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

No, user_ksa is correct. Either (date) or (views) index can be used.

Collectives™ on Stack Overflow

Optimize MySQL order by query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related