Sign In
Not a member yet? Click here to register.
Forgot Password?
Navigation

[GUIDE] Better SQL performance by indexing your table columns

C
ChanSuper Admin
Posted 2 years ago
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.
$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:
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:
$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!
K
Kvido1Member
Posted 2 years ago
Thank you. Interestingly!
C
ChanSuper Admin
Posted 2 years ago
Anytime bro!
K
Kvido1Member
Posted 2 years ago
Takže jsem začal takhle. Je to OK?:
ALTER TABLE fusionhtga4_blog ADD INDEX (blog);

Ale vytváření a rušení indexu není jednoduchou a běžnou akcí,takže jsem dostal strach a zkouším nejprve na localhost. Na ostrém webu implementuji až po testu.
Díky!
Edited by Kvido1 on 19-04-2023 13:22, 2 years ago
C
ChanSuper Admin
Posted 2 years ago
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
Edited by Chan on 27-04-2023 05:19, 2 years ago
You can view all discussion threads in this forum.
You cannot start a new discussion thread in this forum.
You cannot reply in this discussion thread.
You cannot start on a poll in this forum.
You cannot upload attachments in this forum.
You cannot download attachments in this forum.
Moderator: Support Team

Fatal error: Uncaught Error: Class "PHPFusion\Minify" not found in /home/fusion/public_html/themes/templates/layout.php:206 Stack trace: #0 /home/fusion/public_html/themes/templates/footer.php(37): require_once() #1 /home/fusion/public_html/infusions/forum/viewthread.php(30): require_once('/home/fusion/pu...') #2 {main} thrown in /home/fusion/public_html/themes/templates/layout.php on line 206