MYSQL - order by indexed column I have this table with 500,000 rows:

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `views_point` int(10) unsigned NOT NULL DEFAULT '0',
  `publishedon_hourly` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `views_point` (`views_point`),
  KEY `listings` (`publishedon_hourly`,`published`,`cat_id`,`source_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;


I want to make a query like this:

SELECT *
FROM listings
WHERE (`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
    AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ) ) 
AND ( published =1 ) 
AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY views_point DESC
LIMIT 10


This query sometimes works exactly as I want (see this), but according to some opinions, this is incorrect. I searched the web for solutions and found these: 

http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/
http://www.getsymphony.com/discuss/issues/view/657/

I added an index on views_point then tried this query:

select t1.* 
from  listings t1 
left outer join  listings t2 on (t1.views_point=t2.views_point) 
order by t1.views_point 
limit 10


This is the explain:



but I cannot add this condition:

`publishedon_hourly` 
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' ) 
    AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
AND ( published =1 ) 
AND cat_id = 5


I don't know, which one should I assign to (t1 or t2)?

On the other hand what about this way:

SELECT *
FROM listings
WHERE (`publishedon_hourly` 
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' ) 
    AND UNIX_TIMESTAMP( '2015-09-5 23:00:00' ))
AND (published =1)
and views_point is not null
ORDER BY views_point DESC limit 20


If neither one will be good, can any one tell me please how the big blogs get the posts by hits?
When building an INDEX, start with the '=' columns in WHERE.  Then move on to one range:

INDEX(published, publishedon_hourly)  -- or
INDEX(published, cat_id, publishedon_hourly)


In newer versions of MySQL, the second choice may do a leapfrog game (with the IN) to be more efficient than in older versions.  Give it a try.

My cookbook.

You could also give it this one

INDEX(published, views_point)


but that is risky -- It will be fast ood if 20 desired rows show up with high values of views_point, but it will be slow otherwise.  The Optimizer is not smart enough to always choose correctly between handing the ORDER BY versus handling the WHERE.

(As a side note... Did you want the extra noon?  BETWEEN..AND is inclusive.)