
I was thinking more of converting it explicitly to a left join. Unfortunatly people are having a hard time reading my mind today

gulson wrote: anyone knows how to tweak attachment mod?
especially this query:
Hmmm. Im looking forward to thatlanzer wrote: Just finished another site update. Starting work on a message archiving system for phpbb which will move old posts into another database that's transparent to the user. Will post results when I'm finished with it. Hopefully it'll take less than a week...
lanzer wrote: - Viewtopic.php
A list of post_id are obtained, now I slip it into the large queryCode: Select all
$sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p WHERE p.topic_id = $topic_id $limit_posts_time LIMIT $start, " . $board_config['posts_per_page']; if ( !($result = $db->sql_query($sql)) ) { message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql); } while (list($p_id) = $db->sql_fetchrow($result)) { $p_array[] = $p_id; } $post_index = implode(",",$p_array);
Code: Select all
$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_session_time, u.user_allow_viewonline, u.user_allowsmile, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt WHERE p.post_id in ($post_index) AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time $post_time_order";
Code: Select all
SQL Error : 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND pt.post_id = p.post_id AND u.user_id = p.poster_id
SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_session_time, u.user_allow_viewonline, u.user_allowsmile, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.post_id in (,) AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time ASC
Line : 412
File : phpBB2\viewtopic.php
Fearless wrote: All these tweaks are nice. Are there any optimisations you can carry out on a board even if it doesn't have many users/posts ? I heard it's possible to rebuild the search table while removing some words using an exclusion list.
Code: Select all
SELECT word, replacement FROM phpbb_words;
Code: Select all
function obtain_word_list(&$orig_word, &$replacement_word)
{
global $db;
static $s_orig_word, $s_replacement_word;
if(!defined('PHPBB_WORDS_SELECTED'))
{
//
// Define censored word matches
//
$sql = "SELECT word, replacement
FROM " . WORDS_TABLE;
if( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not get censored words from database', '', __LINE__, __FILE__, $sql);
}
if ( $row = $db->sql_fetchrow($result) )
{
do
{
$s_orig_word[] = '#\b(' . str_replace('\*', '\w*?', phpbb_preg_quote($row['word'], '#')) . ')\b#i';
$s_replacement_word[] = $row['replacement'];
}
while ( $row = $db->sql_fetchrow($result) );
}
define('PHPBB_WORDS_SELECTED', true);
}
$orig_word = $s_orig_word;
$replacement_word = $s_replacement_word;
return true;
}
Code: Select all
SELECT post_id FROM phpbb_posts WHERE topic_id = blah LIMIT 300000, 15
Code: Select all
SELECT post_id FROM phpbb_posts WHERE topic_id = blah AND page_number = 1000 LIMIT 15
Code: Select all
| 158188363 | anihq | 192.168.1.2:44763 | anihq | Query | 5 | Sending data | SELECT vote_id FROM bb_vote_voters WHERE vote_id = 586879 AND vote_user_id = 248036 |
Code: Select all
SELECT vote_id FROM bb_vote_voters WHERE vote_id = 586879 AND vote_user_id = 248036;
+---------+
| vote_id |
+---------+
| 586879 |
+---------+
1 row in set (1.67 sec)
Code: Select all
explain SELECT vote_id FROM bb_vote_voters WHERE vote_id = 586879 AND vote_user_id = 248036;
+----------------+------+----------------------+--------------+---------+-------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------------+------+----------------------+--------------+---------+-------+------+-------------+
| bb_vote_voters | ref | vote_id,vote_user_id | vote_user_id | 3 | const | 2020 | Using where |
+----------------+------+----------------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
Code: Select all
create index vote_n_user on bb_vote_voters (vote_user_id, vote_id);
Code: Select all
explain SELECT vote_id FROM bb_vote_voters WHERE vote_id = 586879 AND vote_user_id = 248036;
+----------------+------+----------------------------------+-------------+---------+-------------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------------+------+----------------------------------+-------------+---------+-------------+------+--------------------------+
| bb_vote_voters | ref | vote_id,vote_user_id,vote_n_user | vote_n_user | 6 | const,const | 1 | Using where; Using index |
+----------------+------+----------------------------------+-------------+---------+-------------+------+--------------------------+
1 row in set (0.01 sec)
+---------+
| vote_id |
+---------+
| 586879 |
+---------+
1 row in set (0.00 sec)