Add a DB table index to EasySocial
By User on Friday, 12 March 2021
Posted in General Issues
Replies 11
Likes 0
Views 766
Votes 0
Hello. When I asked server support why does some website pages loads slowly, they say:
You also have many queries running without an index, adding them will greatly reduce the load.


In this topic https://stackideas.com/forums/leader-board-module-and-page-slow-down-website#reply-537053 Sam added a DB table index into `#__social_points_history` and this increased the leaderboard module / leaderboard page query performance.


alter table `#__social_points_history` add index `idx_created` (`created`);


I suppose this pages loads not enough fast:
1. Points Assignment – may be the most slowest page at the moment https://strana-krovi.com/rating
2. All Videos https://strana-krovi.com/videos
3. Dashboard (stream items sort by last modified) https://strana-krovi.com/community
4. All Albums https://strana-krovi.com/photos
5. All members https://strana-krovi.com/badges
6. All badges https://strana-krovi.com/badges (normal load)

Is it possible to add a DB table index by StackIdeas support team?

Or can you teach how to do it? Or may be provide examples of SQL queries? I read few articles about «table index» but it have not a lot examples with Joomla (or EasySocial).

Thanks.
Hi,

Is it possible to add a DB table index by StackIdeas support team?


Regarding the above, we will first need to check if these page has performance issue from the query or not before we can add any table index.

Or can you teach how to do it? Or may be provide examples of SQL queries? I read few articles about «table index» but it have not a lot examples with Joomla (or EasySocial).


For the above, you will need to know which table column require to add index to optimise the db queries. If you can enable the MySQL slow logs then we can check from there if there any any slow queries being logged or not in EasySocial.

Another way to reduce the page load is to display lesser items. E.g. instead of display 50 items from a page, if we can reduce to say 20 items per page, this will also reduce the page load time.
Sam
·
Monday, 15 March 2021 17:46
·
0 Likes
·
0 Votes
·
0 Comments
·
Hello. Yes, I reduced 50 to 30 items per page before but didn't noticed speed up ?‍♂️

Also disabled modules in es-sidebar position to check it.

mysql-slow-query.log can be downloaded with link:
https://strana-krovi.com/mysql-slow-query.log.zip ("crimson-land" database)

Thanks.
User
·
Wednesday, 17 March 2021 14:42
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

Thanks for the query slow logs. We will go through the logs and will check the reported slow queries from EasySocial.

By the way, may i know what is the value you set in 'long_query_time' when you enable the slow log? It seems like some simple queries from Joomla also being logged in the slow log. E.g.



SELECT `id` FROM `#__users` WHERE LOWER(`name`) = 'bosniya na etot raz snajper popal v svoyu tsel';

SELECT * FROM #__languages WHERE published=1 ORDER BY ordering ASC;


Also, after I did a quick view on your log file, it seems like almost every queries are being logged into this slow log ( hence your log file the file size is very huge, almost 1GB after extract the zip file)

Please advise.
Sam
·
Wednesday, 17 March 2021 17:51
·
0 Likes
·
0 Votes
·
0 Comments
·
Hello. For long_query_time = 2
First 24 hours file was empty.
But later it become 900+ mb. (after I publish he’s Es Groups module at Dashboard. Don’t know is it related or not.).
Also there is other sites in log
User
·
Wednesday, 17 March 2021 18:50
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

long_query_time = 2


For the above, i think 2s is too low. It seems to me when you to 2 seconds, your mysql server log almost every queries as slow query.

The default value for this setting is 10 second. Any query that run longer than 10 seconds should be consider as slow queries.

Can you re-adjust this setting to 10 and let clear our existing mysql slow logs so that your mysql server will re-generate the new slow queries?

Please advise.
Sam
·
Wednesday, 17 March 2021 19:48
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi. Yesterday long_query_time was set to 10 and file was cleared.
But today file mysql-slow-query.log is 2.14 GiB

mysql-slow-query.log can be downloaded with link:
https://strana-krovi.com/mysql-slow-query.log.zip ("crimson-land" database)

Thanks.
User
·
Thursday, 18 March 2021 16:32
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi,

Thanks for the heads up on this one.

I've downloaded your slowlogs and we will go through the logs and see which queries from EasySocial needed to be optimised further.

For now, you may disable the mysql slow log so that your server harddisk will not be filled with the the huge log file.

Thanks again and have a nice day
Sam
·
Thursday, 18 March 2021 17:07
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi. Did you have time to look at the logs? Thanks.
User
·
Thursday, 25 March 2021 03:46
·
0 Likes
·
0 Votes
·
0 Comments
·
Hello,

We've logged these issues into our issue tracker and we are currently going through each page you've report to see if we can further optimise the page load or not.

E.g, In Videos page, the reason why the page load is slow was due to the sidebar counters. The queries to retrieve these counters can be expensive ( in terms of performance ). Thus to solve this, in EasySocial V4, we will be loading the sidebar (with counters) with Ajax so that main content of page can load faster. Please see: https://monosnap.com/file/ueRVc1QasuCtZCNcig5L4yt3fhvKI1

Thank you for your patient and your understanding.
Have a nice day
Sam
·
Thursday, 25 March 2021 11:55
·
0 Likes
·
0 Votes
·
0 Comments
·
Thus to solve this, in EasySocial V4, we will be loading the sidebar (with counters) with Ajax so that main content of page can load faster.

perfect, thanx stackideas team. hope you could do more of this optimizations about the loading times.
n00bster
·
Thursday, 25 March 2021 18:10
·
0 Likes
·
0 Votes
·
0 Comments
·
perfect, thanx stackideas team. hope you could do more of this optimizations about the loading times.


Yes, we will see what we can do to optimise the page loading times

Thanks and have a nice day
Sam
·
Friday, 26 March 2021 10:32
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post