Search found 67 matches

by jmpoure
Mon Jan 07, 2008 9:11 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Let's look at this one : 2008-01-04 19:31:15 CET LOG: instruction : SELECT t.*, f.*, tw.notify_status, bm.topic_id as bookmarked, tt.mark_time, ft.mark_time as forum_mark_time FROM phpbb_forums f, phpbb_topics t LEFT JOIN phpbb_topics_watch tw ON (tw.user_id = 103701 AND t.topic_id = tw.topic_id) LE...
by jmpoure
Mon Jan 07, 2008 8:45 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

That one is quite slow: 2008-01-04 19:31:15 CET LOG: instruction : SELECT g.group_id, g.group_name, g.group_colour, g.group_type FROM phpbb_groups g LEFT JOIN phpbb_user_group ug ON ( g.group_id = ug.group_id AND ug.user_id = 1 AND ug.user_pending = 0 ) WHERE g.group_legend = 1 AND (g.group_type <> ...
by jmpoure
Mon Jan 07, 2008 8:27 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Another easy one: 2008-01-04 19:17:30 CET LOG: instruction : SELECT m.*, u.user_colour, g.group_colour, g.group_type FROM phpbb_moderator_cache m LEFT JOIN phpbb_users u ON (m.user_id = u.user_id) LEFT JOIN phpbb_groups g ON (m.group_id = g.group_id) WHERE m.display_on_index = 1 AND m.forum_id = 23 ...
by jmpoure
Mon Jan 07, 2008 7:53 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

This is a nasty one: 2008-01-04 17:51:51 CET LOG: instruction : SELECT p.post_id FROM phpbb_posts p WHERE p.topic_id = 215 AND p.post_approved = 1 ORDER BY p.post_time DESC LIMIT 48 OFFSET 0 2008-01-04 17:51:51 CET LOG: durée : 131.323 ms Let's look at the query plan: EXPLAIN ANALYSE SELECT p.post_i...
by jmpoure
Mon Jan 07, 2008 7:44 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

This one is very long: 2008-01-04 17:39:35 CET LOG: instruction : 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.u...
by jmpoure
Mon Jan 07, 2008 5:31 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

I got the reply from PostgreSQL : for a 19 row table, a sequential access is faster.
But you should change indexes for larger table.
by jmpoure
Mon Jan 07, 2008 2:55 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Dear David, I am looking at the statics table in PostgreSQL, to see when the database does sequential scans. Do : select * from pg_tables On my server, I can see that phpbb_banlist results in hundred thousands of disc access : I found a query in my logs, that may be responsible: explain analyse SELE...
by jmpoure
Mon Jan 07, 2008 2:13 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Do you plan to integrate PostgreSQL tseach2 search : viewtopic.php?f=4&t=28707&st=0&sk=t&sd=a
by jmpoure
Mon Jan 07, 2008 2:06 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Fine, thanks. Do you see any quick fix to remove these warnings?
by jmpoure
Mon Jan 07, 2008 7:58 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 87888

Re: SQL optimisation

Are you using pg_ escape_ string instead of addslashes ?
Seems like we should be using a special class in case of PostgreSQL when generating templates.