Field range with order by, filesort problem

Geteburg

Hey!

Got a bit of a problem that i cannot solve.

I've got a table, and i need to get some results from it. The problem is of course filesort.
I know why filesort is in use but cannot get rid of it.

Important fields:
- active
- points
- date_added

Index's are set..

SELECT ... WHERE active = '1' AND points <= '2' ORDER BY date_added DESC

CREATE TABLE IF NOT EXISTS `entries` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `source_url` varchar(200) collate utf8_unicode_ci NOT NULL,
  `views` int(11) NOT NULL default '0',
  `text` text collate utf8_unicode_ci NOT NULL,
  `active` tinyint(1) NOT NULL default '0',
  `points` int(11) NOT NULL default '0',
  `approved_by` int(11) NOT NULL,
  `approve_date` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `points` (`points`),
  KEY `ind_active_points_date` (`active`,`points`,`date_added`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2065 ;

1. mysql> explain SELECT * FROM entries FORCE INDEX (ind_active_points_date) WHERE active = 1 AND points <= 2 ORDER BY date_added DESC;
2. +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+
5. | 1 | SIMPLE | entries | range | ind_active_points_date | ind_active_points_date | 4 | NULL | 1 | Using where; Using filesort |
6. +----+-------------+------------+-------+------------------------+------------------------+---------+------+------+-----------------------------+
7. 1 row in set (0.01 sec)

Of course the problem is that i want results that have 2 or less in points field, and use ORDER BY on different field.

I'm stuck here.. I even tried with ALTER table to first change the order of the table, and then use the above query without ORDER BY. Since right now table is at 2000 rows only, ALTER table worked quite fast. I'm not too sure about it when it comes to 2M+ rows.

I know why the problem is there, but since i'm not too advance with sql i can't solve it really.

Any suggestions how to solve this?

sheephat

is it not because of your indexes (if any) that the filesort is there?

Geteburg

Yes, the reason for filesort is because index is ignored when you do range on 1 field and order by on a different field.

SELECT ... points <= 2 ORDER BY date_added DESC

This part is the reason.

I'm pretty sure its a dead end with just 1 query. The real question should be something like: How to get results from 1 table that have points <= 2 and order by date DESC!

2 or 3 queries needed? Fine by me, but i simply cannot figure it out :S