I was examining slow queries in my database. The slowest was

SELECT t.*, p.root_level, p.message_time, p.message_subject, p.icon_id, p.to_address, p.message_attachment, p.bcc_address, u.username, u.username_clean, u
.user_colour
FROM phpbb_privmsgs_to t, phpbb_privmsgs p, phpbb_users u
WHERE t.user_id = N
AND p.author_id = u.user_id
AND t.folder_id = N
AND t.msg_id = p.msg_id
ORDER BY p.message_time DESC
LIMIT N

My Info about phpbb tables
phpbb_privmsgs_to 5919959 rows
phpbb_privmsgs 4055176 rows
phpbb_users 72249 rows

Sorting by p.message_time forces mysql to create a temporary table

ID SELECT TYPE TABLE TYPE POSSIBLE KEYS KEY KEY LEN REF ROWS EXTRA
1 SIMPLE t ref msg_id,usr_flder_id usr_flder_id 7 const,const 359 Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY,author_id PRIMARY 3 new_bb.t.msg_id 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 3 new_bb.p.author_id 1

Elapsed: 1.96515s

I believe that the order of messages can be sorted not by message_time as it takes long time but by msg_id. It takes less time to complete the query for me and uses much less resouses.

Here is a simple query

explain SELECT t.*, p.root_level, p.message_time, p.message_subject, p.icon_id, p.to_address, p.message_attachment, p.bcc_address, u.username, u.username_clean, u.user_colour
FROM phpbb_privmsgs_to t, phpbb_privmsgs p, phpbb_users u
WHERE t.user_id = 5351
AND p.author_id = u.user_id
AND t.folder_id = 0
AND t.msg_id = p.msg_id
ORDER BY t.msg_id DESC
LIMIT 50

1 SIMPLE t ref msg_id,usr_flder_id usr_flder_id 7 const,const 359 Using where; Using filesort
1 SIMPLE p eq_ref PRIMARY,author_id PRIMARY 3 new_bb.t.msg_id 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 3 new_bb.p.author_id 1
Elapsed: 0.00241s

What can community tell about this fact? Can ordering by msg_id differ from ordering by message time? I think that messages will be in the same order. But sorting this way is much faster.