[GUIDE] Better SQL performance by indexing your table columns: General Addon and Modification Support

Home
Search

Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.

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 Download source

$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 Download source

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 Download source

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

0 replies

Thank you. Interestingly!

1 reply

Takže jsem začal takhle. Je to OK?:

Code Download source

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 14:22, 1 y

0 replies

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

Code Download source

 $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 Download source

 $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 Download source

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 Download source

$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 Download source

$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 Download source