Order posts strictly by post_id instead of post_time, post_id

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Post Reply
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

Hello, I have a 15+ million phpbb_posts database, these posts were migrated from somewhere else and some of them have incorrect times (non-UTC) this causes the first post (OP) sometimes to be wrong since it's older (has local time) than some other UTC ones.
I've tried changing the following phpbb lines with no success:
viewtopic.php - LINE 451:

Code: Select all

$sort_by_sql = array('a' => array('u.username_clean', 'p.post_id'), 't' => array('p.post_time', 'p.post_id'), 's' => array('p.post_subject', 'p.post_id'));
to

Code: Select all

$sort_by_sql = array('a' => array('u.username_clean', 'p.post_id'), 't' => array('p.post_id', 'p.post_time'), 's' => array('p.post_subject', 'p.post_id'));
this makes the query take wayyy to much time, perhaps it's missing an index or something.

viewtopic.php - LINE 1193:

Code: Select all

	ORDER BY $sql_sort_order";
to

Code: Select all

	ORDER BY p.post_id ASC;
this makes the first page of a topic load fine but any of the other pages take way too long to load.

Not sure how to do this properly or if/how to create a proper index for it, if possible I'd like to do the same for topics. Any help would be appreciated.
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

Should go to custom coding, but anyway:
Did you check if your posts-table has the correct primary index using column post_id as only column? (Name: PRIMARY, unique)


Edit: thanks for moving, was just on the way to create a report...
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

canonknipser wrote: Thu Jan 10, 2019 1:16 pm Should go to custom coding, but anyway:
Did you check if your posts-table has the correct primary index using column post_id as only column? (Name: PRIMARY, unique)


Edit: thanks for moving, was just on the way to create a report...
So I installed phpbb yesterday and had to make some adaptations to the schema before migrating my data overnight, mainly changing occurrences of user_id, post_id and topic_id to bigint instead of the default int/mediumint, to my surprise there was no index on post_id by default, is this not intended? I just had to create one
Image
that last one I had to create just now, the other ones came with phpbb, post_id is the primary key
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

hanzzzzzzzzzzz wrote: Thu Jan 10, 2019 1:41 pm to my surprise there was no index on post_id by default, is this not intended?
No, it's not intended, see a screen cap from a local test installation of mine:
screen70.jpg
I suspect the index was dropped by the database system when you changed the data types:
hanzzzzzzzzzzz wrote: Thu Jan 10, 2019 1:41 pm mainly changing occurrences of user_id, post_id and topic_id to bigint instead of the default int/mediumint,
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

canonknipser wrote: Thu Jan 10, 2019 1:51 pm
hanzzzzzzzzzzz wrote: Thu Jan 10, 2019 1:41 pm to my surprise there was no index on post_id by default, is this not intended?
No, it's not intended, see a screen cap from a local test installation of mine:
screen70.jpg
I suspect the index was dropped by the database system when you changed the data types:
hanzzzzzzzzzzz wrote: Thu Jan 10, 2019 1:41 pm mainly changing occurrences of user_id, post_id and topic_id to bigint instead of the default int/mediumint,
It seems I'm missing alot of indexes then, here's the SQL of the changes I made to the schema https://pastebin.com/raw/vQBPs11s. Perhaps that's what made the indexes disappear yeah, I wasn't aware that was a thing. I'll try to add them again and report back.

Edit: I did a fresh install of phpbb again on a local test machine and there was no index on post_id again, no idea what's going on
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

Wondering why you changed all those columns to bigint? Do you expect more than 4 Billion posts, topics, users?

post_id should be created as the primary key with the predefined name PRIMARY for the table, maybe for that it is not listed in the index list by your tool, but there is a index on primary keys anyway (assuming you are using mysql), see https://dev.mysql.com/doc/refman/8.0/en ... dexes-keys
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

canonknipser wrote: Thu Jan 10, 2019 2:34 pm Wondering why you changed all those columns to bigint? Do you expect more than 4 Billion posts, topics, users?

post_id should be created as the primary key with the predefined name PRIMARY for the table, maybe for that it is not listed in the index list by your tool, but there is a index on primary keys anyway (assuming you are using mysql), see https://dev.mysql.com/doc/refman/8.0/en ... dexes-keys
The data I migrated just had these ridiculously big IDs even though they weren't necessary or realistic, no idea how they were so big really, rather than going through the trouble of adapting them all and lowering them across the board I decided to just increase the php thingy to bigint and migrate it as it was. Could you post the indexes you have on your installation for phpbb_users and phpbb_topics just so I could check if post_id was the only missing one? actually if you could export just the schema so I could compare it with mine it would be great. Thanks in advance.
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

topics
topic indices
topic indices
users
user indices
user indices
SQL (complete DB from a 3.2.4 installation, afaik there where no database changes in 3.2.5)
qi_qi_3_2_4_basic.txt
sql
(67.17 KiB) Downloaded 17 times
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

I didn't notice much difference, indeed the PRIMARY KEY is a index by default. I noticed the slow log shows some really awful timings for phpbb's when sorting by post_id

Code: Select all

# Query_time: 15.811073  Lock_time: 0.000059 Rows_sent: 35  Rows_examined: 1353453
SET timestamp=1547135395;
SELECT t.topic_id FROM (phpbb_topics t) WHERE t.forum_id = 2
		AND t.topic_type IN (0, 1)
		 AND (t.topic_visibility = 1)
		 ORDER BY t.topic_type DESC, t.topic_last_post_time DESC, t.topic_last_post_id DESC
		 
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     9
# Query_time: 2871.762946  Lock_time: 0.064182 Rows_sent: 0  Rows_examined: 939519
SET timestamp=1547112242;
SELECT post_id, post_time, post_username, post_subject, post_text, bbcode_uid, bbcode_bitfield, p.forum_id, f.forum_name, u.user_id, u.username, u.user_colour
			FROM phpbb_posts p
			JOIN phpbb_users u ON (u.user_id = p.poster_id)
			JOIN phpbb_forums f ON (f.forum_id = p.forum_id)
			WHERE NOT EXISTS (SELECT topic_id FROM phpbb_topics WHERE topic_id = p.topic_id);

# User@Host: root[root] @ localhost [127.0.0.1]  Id:    39
# Query_time: 203.864063  Lock_time: 0.000198 Rows_sent: 18  Rows_examined: 122739
SET timestamp=1547114616;
SELECT p.post_id
	FROM phpbb_posts p
	WHERE p.topic_id = 2000000
		AND (p.post_visibility = 1)
		
		
	ORDER BY p.post_id ASC
Image


Image


Image
it seems sorting by post_id sucks even with the index
Last edited by hanzzzzzzzzzzz on Thu Jan 10, 2019 3:55 pm, edited 1 time in total.
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

That seems to be the case because the used indices don't contain the order by-columns, so depending on the mysql engine, that can be a reason for a
time consuming filesort, together with the overall performance of the mysql server.

Database query optimization is a time and brain consuming business, because every query and every server has it's special "problems" ...

Maybe you can play around creating some extra indices for the tables, eg. a key on forum_id ascending, post_visibility ascending, post_id descending.
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

canonknipser wrote: Thu Jan 10, 2019 4:37 pm That seems to be the case because the used indices don't contain the order by-columns, so depending on the mysql engine, that can be a reason for a
time consuming filesort, together with the overall performance of the mysql server.

Database query optimization is a time and brain consuming business, because every query and every server has it's special "problems" ...

Maybe you can play around creating some extra indices for the tables, eg. a key on forum_id ascending, post_visibility ascending, post_id descending.
I tried the DESC index on post_id but it didn't change anything, doesn't even show up as a "path" with EXPLAIN
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Order posts strictly by post_id instead of post_time, post_id

Post by canonknipser »

Only post_id or a combined key from all 3 columns?
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
hanzzzzzzzzzzz
Registered User
Posts: 8
Joined: Thu Jan 10, 2019 12:35 pm

Re: Order posts strictly by post_id instead of post_time, post_id

Post by hanzzzzzzzzzzz »

canonknipser wrote: Fri Jan 11, 2019 6:05 am Only post_id or a combined key from all 3 columns?
Only on post_id at first which didn't give me very good results, now I tested with all three in one (compound?) and it seems slightly better
Post Reply

Return to “phpBB Custom Coding”