Official Home of the PHPFusion CMS

Posted by Chan on 11-04-2023 11:17

When you create an index on a column, MySQL stores a copy of that column’s data in a separate structure called an index table. This makes it faster for MySQL to find rows that match a WHERE clause that refers to that column.

In general, you should create indexes on columns that are frequently used in WHERE clauses. If you have a table with many columns, you may want to create an index on a combination of columns. This can help MySQL find rows that match WHERE clauses that refer to multiple columns.

I'll try to go simple and short with this tutorial. Please consider this query.

Code

$q = dbquery("SELECT a.alpha,a.beta,a.charlie,a.delta,b.echo, b.golf, b.hotel FROM ".DB_ALPHA." a INNER JOIN ".DB_BETA." b WHERE a.beta='1' AND a.charlie='2' ORDER BY a.delta";

The above of the code could be slow. To improvise on the above query performance, you will need to add index on the columns on the WHERE statement. Since the WHERE part is only on DB_ALPHA, then we need to add modifications on this table with such:

Code

dbquery("ALTER TABLE ".DB_ALPHA." ADD INDEX alpha ( beta, charlie)";

Now, your index is ready but it is not used yet and to utilize this new change we need to revise our query code to the following:

Code

$q = dbquery("SELECT a.alpha,a.beta,a.charlie,a.delta,b.echo, b.golf, b.hotel FROM ".DB_ALPHA." a use index(alpha) INNER JOIN ".DB_BETA." b WHERE a.beta='1' AND a.charlie='2' ORDER BY a.delta";

Now you can try it out, and see if it helps!

Edited by Chan on 11-07-2026 14:03

Posted by Chan on 27-04-2023 06:08

For Blog index page, you need to examine this query, currently in the code:

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:

Code

 $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:

Code

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:

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." 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:

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 ";

Implemented Changes:

Code

".DB_BLOG." USE index(blogs) AS b

Edited by Chan on 27-04-2023 06:19