MySQL: Use 2 keys, one to join, one to sort?

Question

I have a simple where clause on a keyed field, a join between two tables on a different field, and then a sort on another field of the second table. I have keys on everything, but it seems that the describe is giving me a filesort and a 30 second query execution :

mysql> describe  SELECT * FROM `alias` LEFT OUTER JOIN `aliaspoint` ON (`alias`.`id` = `aliaspoint`.`alias_id`) WHERE `alias`.`type_id` = 9  ORDER BY `aliaspoint`.`points` DESC LIMIT 100 OFFSET 200; 
+----+-------------+---------------------+--------+------------------------+------------------------+---------+------------------------+--------+---------------------------------+
| id | select_type | table               | type   | possible_keys          | key                    | key_len | ref                    | rows   | Extra                           |
+----+-------------+---------------------+--------+------------------------+------------------------+---------+------------------------+--------+---------------------------------+
|  1 | SIMPLE      | metaward_alias      | ref    | metaward_alias_type_id | metaward_alias_type_id | 4       | const                  | 356710 | Using temporary; Using filesort |
|  1 | SIMPLE      | metaward_aliaspoint | eq_ref | alias_id               | alias_id               | 4       | paul.metaward_alias.id |      1 |                                 |
+----+-------------+---------------------+--------+------------------------+------------------------+---------+------------------------+--------+---------------------------------+
2 rows in set (7.52 sec)

Why is it not using the key for aliaspoint on points for the orderby?

Tables :

mysql> show create table aliaspoint;
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                                                                                                                                                                                                        |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aliaspoint | CREATE TABLE `aliaspoint` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alias_id` int(11) NOT NULL,
  `points` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias_id` (`alias_id`),
  KEY `aliaspoint_points` (`points`)
) ENGINE=MyISAM AUTO_INCREMENT=1014683 DEFAULT CHARSET=latin1 |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

By "keys" I presume you mean indexed columns.

Generally speaking a RDBMS will use one index and one index only to access a table.

The optimiser has obviously decided to use an index to filter out the "where =" and then sort the remulting output. It could use the index on hte order by column to get all the rows in the right sequence and then filter out the others - but - that would mess up the join.

If you think about it you will see its difficult to do anything else. Once you've selected a row into a result set the index is pretty useless as its pointing to a row on disk.

You could try putting all three columns into one index which might enable some more tricks.

Answer 2

Why is it not using the key for aliaspoint on points for the orderby?

Because indexes are only used for data retrieval. It is the returned rows which need sorting.