For Blog index page, you need to examine this query, currently in the code:
$condition = "SELECT b.*, bu.user_id, bu.user_name, bu.user_status, bu.user_avatar, bu.user_level, bu.user_joined, ($sql_sum) AS sum_rating, ($sql_count) AS count_votes, (SELECT COUNT(bcc.comment_id) FROM ".DB_COMMENTS." AS bcc WHERE bcc.comment_item_id = b.blog_id AND bcc.comment_type = 'B') AS comments_count, MAX(b.blog_datestamp) AS last_updated
FROM ".DB_BLOG." AS b
LEFT JOIN ".DB_USERS." AS bu ON b.blog_name=bu.user_id
{FILTER_JOIN}
WHERE {MULTILANG_CONDITION} {VISIBILITY} AND (blog_start=0 || blog_start<=:start_time) AND (blog_end=0 || blog_end>=:end_time) AND blog_draft=0 {ARCHIVE_CONDITION}
GROUP BY b.blog_id ORDER BY blog_sticky DESC, {FILTER_CONDITION} LIMIT :rowstart, :limit ";
$sql = strtr($condition, [
'{FILTER_JOIN}' => $filter_join,
'{MULTILANG_CONDITION}' => (multilang_table('BL') ? in_group('blog_language', LANGUAGE)." AND " : ""),
'{VISIBILITY}' => groupaccess('blog_visibility'),
'{ARCHIVE_CONDITION}' => $archiveSql,
'{FILTER_CONDITION}' => $filter_condition, ]);
As you can see, blog
WHERE condition is searching on
blog_language
,
blog_visibility
,
blog_datestamp
and
blog_reads
, we also can add
blog_id
,
blog_cat
,
And in author search, we also have this query:
$sql = "SELECT b.*, bu.user_id, bu.user_name, bu.user_status, bu.user_avatar , bu.user_level, bu.user_joined, ($sql_sum) AS sum_rating, ($sql_count) AS count_votes, (SELECT COUNT(bc.comment_id) FROM ".DB_COMMENTS." AS bc WHERE bc.comment_item_id = b.blog_id AND bc.comment_type = 'B') AS comments_count, MAX(b.blog_datestamp) AS last_updated FROM ".DB_BLOG." AS b INNER JOIN ".DB_USERS." AS bu ON b.blog_name=bu.user_id ".(multilang_table('BL') ? "WHERE ".in_group('blog_language', LANGUAGE)." AND " : "WHERE ").groupaccess('blog_visibility')." AND (blog_start=0 || blog_start<=".time().") AND (blog_end=0 || blog_end>=".time().") AND blog_draft=0 AND blog_name=:author_id GROUP BY blog_id ORDER BY blog_sticky DESC, ".$filter_condition." LIMIT :rowstart, :limit ";
WHERE ...
blog_start
,
blog_end
,
blog_draft
,
blog_sticky
, and finally
blog_name
also is a condition in the above SQL statement.
So, these columns can be used to the index as such:
ALTER TABLE andromeda_blog ADD INDEX blogs( blog_id, blog_cat, blog_language, blog_visibility, blog_datestamp, blog_reads, blog_start, blog_end, blog_draft, blog_sticky, blog_name )
To use for example, change the above from:$condition = "SELECT b.*, bu.user_id, bu.user_name, bu.user_status, bu.user_avatar, bu.user_level, bu.user_joined, ($sql_sum) AS sum_rating, ($sql_count) AS count_votes, (SELECT COUNT(bcc.comment_id) FROM ".DB_COMMENTS." AS bcc WHERE bcc.comment_item_id = b.blog_id AND bcc.comment_type = 'B') AS comments_count, MAX(b.blog_datestamp) AS last_updated FROM ".DB_BLOG." USE index(blogs) AS b LEFT JOIN ".DB_USERS." AS bu ON b.blog_name=bu.user_id {FILTER_JOIN} WHERE {MULTILANG_CONDITION} {VISIBILITY} AND (blog_start=0 || blog_start<=:start_time) AND (blog_end=0 || blog_end>=:end_time) AND blog_draft=0 {ARCHIVE_CONDITION} GROUP BY b.blog_id ORDER BY blog_sticky DESC, {FILTER_CONDITION} LIMIT :rowstart, :limit ";
to:
$condition = "SELECT b.*, bu.user_id, bu.user_name, bu.user_status, bu.user_avatar, bu.user_level, bu.user_joined, ($sql_sum) AS sum_rating, ($sql_count) AS count_votes, (SELECT COUNT(bcc.comment_id) FROM ".DB_COMMENTS." AS bcc WHERE bcc.comment_item_id = b.blog_id AND bcc.comment_type = 'B') AS comments_count, MAX(b.blog_datestamp) AS last_updated FROM ".DB_BLOG." AS b LEFT JOIN ".DB_USERS." AS bu ON b.blog_name=bu.user_id {FILTER_JOIN} WHERE {MULTILANG_CONDITION} {VISIBILITY} AND (blog_start=0 || blog_start<=:start_time) AND (blog_end=0 || blog_end>=:end_time) AND blog_draft=0 {ARCHIVE_CONDITION} GROUP BY b.blog_id ORDER BY blog_sticky DESC, {FILTER_CONDITION} LIMIT :rowstart, :limit ";
Implemented Changes:
".DB_BLOG." USE index(blogs) AS b