Does anyone know why MySQL does not use indices/keys for the following query despite the fact it has plenty of indices to choose from ... mysql> EXPLAIN SELECT DISTINCT m.*, r.*, u.name, u.uid FROM cvs_messages m INNER JOIN cvs_files f ON m.cid = f.cid INNER JOIN cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT 0, 10; +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ | m | ALL | PRIMARY,rid_created,cid_created,uid | NULL | NULL | NULL | 15732 | Using temporary; Using filesort | | f | ref | cid,cid_2 | cid | 4 | m.cid | 3 | Using index | | r | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where | | u | eq_ref | PRIMARY | PRIMARY | 4 | m.uid | 1 | | +-------+--------+-------------------------------------+--------- +---------+-------+-------+---------------------------------+ mysql> EXPLAIN cvs_messages; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | cid | int(10) unsigned | | PRI | 0 | | | rid | int(10) unsigned | | MUL | 0 | | | created | int(10) unsigned | | MUL | 0 | | | user | varchar(255) | | MUL | | | | message | text | YES | | NULL | | | uid | int(10) unsigned | | MUL | 0 | | +---------+------------------+------+-----+---------+-------+ mysql> EXPLAIN cvs_files; +---------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+-------+ | cid | int(10) unsigned | | MUL | 0 | | | rid | int(10) unsigned | | MUL | 0 | | | file | varchar(255) | | MUL | | | | branch | varchar(255) | | MUL | | | | revision | varchar(255) | | | | | | lines_added | int(10) unsigned | | | 0 | | | lines_removed | int(10) unsigned | | | 0 | | | nid | int(10) unsigned | | MUL | 0 | | | uid | int(10) unsigned | | MUL | 0 | | +---------------+------------------+------+-----+---------+-------+ mysql> EXPLAIN cvs_repositories; +------------+---------------------+------+-----+--------- +----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+--------- +----------------+ | rid | int(10) unsigned | | PRI | NULL | auto_increment | | name | varchar(255) | | UNI | | | | root | varchar(255) | | | | | | modules | varchar(255) | | | | | | diffurl | varchar(255) | | | | | | newurl | varchar(255) | | | | | | trackerurl | varchar(255) | | | | | | method | tinyint(2) unsigned | | | 0 | | | updated | int(11) unsigned | | | 0 | | +------------+---------------------+------+-----+--------- +----------------+ -- Dries Buytaert :: http://www.buytaert.net/