[SOLVED] Field range, order by, filesort problem

[CrustyDOD]

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

 

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? Even if 3 queries are needed i would be happy.

[fenway]

Post the table structure... what indexes do you have?

[CrustyDOD]

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',
  `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=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2065 ;

 

Don't think indexes will be important here since i have 'range' on 1 field and order by on different field. As far as i know, this is a NO-GO situation and filesort will always be used.. Am i wrong?

[fenway]

Can we see the EXPLAIN output?

[CrustyDOD]

Oops, forgot to post it :S

Sorry about that.

 

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

 

[fenway]

I meant without the FORCE INDEX....

[CrustyDOD]

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

[fenway]

I'm not a big fan of re-posting question to multiple forums -- solution is here; next time, please let us know when to stop wasting our time.