Search found 8 matches

by hanzzzzzzzzzzz
Wed Jan 16, 2019 5:18 pm
Forum: phpBB Custom Coding
Topic: phpBB manualy create forum posts with SQL query
Replies: 4
Views: 379

Re: phpBB manualy create forum posts with SQL query

You're gonna need some way to generate the bbcode_uid or whatever, I inserted a ton of posts through SQL and all of the bbcode parsing was missing
by hanzzzzzzzzzzz
Sat Jan 12, 2019 4:25 am
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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
by hanzzzzzzzzzzz
Fri Jan 11, 2019 3:05 am
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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,...
by hanzzzzzzzzzzz
Thu Jan 10, 2019 3:39 pm
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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 # Query_time: 15.811073 Lock_time: 0.000059 Rows_sent: 35 Rows_examined: 1353453 SET timestamp=1547135395; SELECT t.topic_id FROM ...
by hanzzzzzzzzzzz
Thu Jan 10, 2019 2:41 pm
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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...
by hanzzzzzzzzzzz
Thu Jan 10, 2019 2:02 pm
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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: mainly changing occurrences of user_id,...
by hanzzzzzzzzzzz
Thu Jan 10, 2019 1:41 pm
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

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

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...
by hanzzzzzzzzzzz
Thu Jan 10, 2019 12:42 pm
Forum: phpBB Custom Coding
Topic: Order posts strictly by post_id instead of post_time, post_id
Replies: 12
Views: 535

Order posts strictly by post_id instead of post_time, post_id

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 li...

Go to advanced search