Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 3.0.7-PL1
-
Fix Version/s: 3.0.13-RC1
-
Component/s: Database Abstraction Layer (DBAL)
-
Labels:
-
Environment:Linux ivbb.ru 2.6.32-21-server #32-Ubuntu SMP Fri Apr 16 09:17:34 UTC 2010 x86_64
PHP Version 5.3.8
MySQL 5.5.21-log
Description
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.


Ordering by id is not really the best, but an index could be added for the time