Hi there, please check below
my table: CREATE TABLE `news` ( `news_id` int(11) NOT NULL auto_increment, `provider_id` int(11) NOT NULL default '0', `provider_news_id` char(50) default NULL, `category` char(50) NOT NULL default '', `heading` char(50) NOT NULL default '', `discription` char(255) default NULL, `link` char(255) NOT NULL default '', `source` char(50) NOT NULL default '', `date` timestamp(14) NOT NULL, `provider_date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`news_id`), UNIQUE KEY `provider_id` (`provider_id`,`provider_news_id`), KEY `provider_date` (`provider_date`), KEY `provider_news_id` (`provider_news_id`), KEY `category_provider_date` (`category`,`provider_date`) ) TYPE=MyISAM the keys: mysql> show index from news; +-------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +-------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+---------+ | news | 0 | PRIMARY | 1 | news_id | A | | 1930 | NULL | NULL | | | news | 0 | provider_id | 1 | provider_id | A | | NULL | NULL | NULL | | | news | 0 | provider_id | 2 | provider_news_id | A | | NULL | NULL | NULL | | | news | 1 | provider_date | 1 | provider_date | A | | 1930 | NULL | NULL | | | news | 1 | provider_news_id | 1 | provider_news_id | A | | 1930 | NULL | NULL | | | news | 1 | category_provider_date | 1 | category | A | | 5 | NULL | NULL | | | news | 1 | category_provider_date | 2 | provider_date | A | | 1930 | NULL | NULL | | +-------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+---------+ 7 rows in set (0.00 sec) my query: select * from news where category = 'x' order by category DESC, provider_date DESC; explain query: mysql> explain select * from news where category = 'x' order by category DESC, provider_date DESC; +-------+------+------------------------+------------------------+---------+-------+------+----------------------------+ | table | type | possible_keys | key | key_len | ref | |rows | Extra | +-------+------+------------------------+------------------------+---------+-------+------+----------------------------+ | news | ref | category_provider_date | category_provider_date | 50 | const | | 1 | where used; Using filesort | +-------+------+------------------------+------------------------+---------+-------+------+----------------------------+ 1 row in set (0.00 sec) Why is filesort used here? According to the MySQL doc (5.2.7, example 5) this should work using the index without any additional sorting. btw: MySQL-3.23.49-max-log running on FreeBSD 4.4-RELEASE Thanks Regards Sven Huster --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
