Development Discussion Board

Feel free to choose your prefered optimisations, and drop the others. When you are finished with modifications, I am interested in creating a set of 5 to 10 materialized view in PL. We could be using http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html Together with tseach2, it...

DavidMJ wrote:

jmpoure wrote:I am in charge of handling the various databases that phpBB is supported on.

I always do my query work on PostgreSQL as it is the most strict

Cool.

Look at that. We discussed about this Zebra thing before: SELECT u.*, z.friend, z.foe, p.* FROM phpbb_users u, phpbb_posts p LEFT JOIN phpbb_zebra z ON (z.user_id = 1 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('421984', '421983', '421982', '421981', '421980', '421979', '421978', '421977', '42...

DavidMJ wrote:

jmpoure wrote:

Code: Select all

CREATE INDEX phpbb_forums_left_id
  ON phpbb_forums
  USING btree
  (right_id);

That looks like a bug, no?

Yes, fixed in my previous message.

Wow, I cannot believe we have missed this... Thanks for that! The best solution is when all queries run between 0 and 5 millisecond. I believe you should develop on PostgreSQL and port to other databases. pgAdmin3 gives you access to all statistics in a nice GUI. Some fixes are just minor, because ...

I don't know if we discussed about this one: 2008-01-07 23:06:50 CET LOG: instruction : SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'cXXXXXXXXXXXXXXXX9' AND u.user_id = s.session_user_id 2008-01-07 23:06:50 CET LOG: durée : 37.006 ms Query plan: QUERY PLAN -------------...

2008-01-07 23:04:58 CET LOG: instruction : UPDATE phpbb_config SET config_value = 'eXXXXXXXXXXXXXae6' WHERE config_name = 'rand_seed' 2008-01-07 23:04:58 CET LOG: durée : 26.001 ms There should be an index on config_name : CREATE INDEX phpbb_config_config_name ON phpbb_config USING btree (config_na...

Olay, this is enough for me.
Talk to you next time.

CREATE INDEX phpbb_forums_left_right_id ON phpbb_forums USING btree (left_id, right_id); Should be: CREATE INDEX phpbb_forums_right_id ON phpbb_forums USING btree (right_id); AND CREATE INDEX phpbb_forums_right_id ON phpbb_forums USING btree (right_id); This kind of query is too slow: 2008-01-04 21...

This is too slow: 2008-01-04 21:53:58 CET LOG: instruction : SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_ip, s.session_viewonline FROM phpbb_users u, phpbb_sessions s WHERE s.session_time >= 1199479738 AND u.user_id = s.session_user_...