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.
Not a member yet? Click here to register.
Forgot Password?

[GUIDE] Better SQL performance by indexing your table columns

Pinned Asked Modified Viewed 2,161 times
C
Chan
C
Chan 0
Lead Developer of PHP-Fusion
  • Super Admin, joined since
  • Contributed 3,842 posts on the community forums.
  • Started 232 threads in the forums
  • Started this discussions
  • Answered 6 questions
asked
Super Admin

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!
0 replies

3 posts

K
Kvido1
K
  • Member, joined since
  • Contributed 61 posts on the community forums.
  • Started 16 threads in the forums
answered
Member

Thank you. Interestingly!
1 reply
K
Kvido1
K
  • Member, joined since
  • Contributed 61 posts on the community forums.
  • Started 16 threads in the forums
answered
Member

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 14:22,
0 replies
C
Chan
C
Chan 0
Lead Developer of PHP-Fusion
  • Super Admin, joined since
  • Contributed 3,842 posts on the community forums.
  • Started 232 threads in the forums
  • Started this discussions
  • Answered 6 questions
answered
Super Admin

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 06:19,
0 replies

Category Forum

Tutorials

Statistics

  • Views 0 views
  • Posts 3 posts
  • Votes 0 votes
  • Topic users 2 members

0 participants

Notifications

Track thread

You are not receiving notifications from this thread.

Related Questions

Not yet