StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimize MySQL order by query
text
Body
copied!<p>I have the following query to get the most viewed articles that submitted in last month for example.</p> <pre><code>explain SELECT * FROM article WHERE date > 1315391769 ORDER BY views DESC LIMIT 10 </code></pre> <p>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?</p> <p>This is the table scheme with the current indexes I tried:</p> <pre><code>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); </code></pre> <p>Notice: I also tried mysql date instead of Unix timestamp but I got the same result.</p> <p>This is the output of EXPLAIN:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload