Score Feature - Slow Database Queries

• Error

It seems that the greater the number of users exist in the wiki, tied with a higher total score per user, generates very slow queries to the database, resulting in a very slow load time per page. I was experiencing 20 seconds + of load time per wiki page, as it was loading the scoring table every time.

Some samples from the database (not sure if useful).

1. Time: 170131 16:54:54
2. User@Host: giswikiadmgiswikiadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 334497
3. Query_time: 28.076675 Lock_time: 0.000208 Rows_sent: 10 Rows_examined: 4229506

SET timestamp=1485899694;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. User@Host: eiadwikiadmeiadwikiadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344609
2. Query_time: 21.994789 Lock_time: 0.000448 Rows_sent: 10 Rows_examined: 3325313

SET timestamp=1485955286;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. Time: 170201 8:21:56
2. User@Host: eiadwikiadmeiadwikiadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344614
3. Query_time: 22.215687 Lock_time: 0.000448 Rows_sent: 10 Rows_examined: 3325313

SET timestamp=1485955316;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. Time: 170201 8:22:42
2. User@Host: eiadwikiadmeiadwikiadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344620
3. Query_time: 21.846001 Lock_time: 0.000245 Rows_sent: 10 Rows_examined: 3325313

SET timestamp=1485955362;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. Time: 170201 8:23:33
2. User@Host: citypediaadmcitypediaadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344638
3. Query_time: 12.665043 Lock_time: 0.000552 Rows_sent: 10 Rows_examined: 2035464

use citypediap1;
SET timestamp=1485955413;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. Time: 170201 8:23:44
2. User@Host: eiadwikiadmeiadwikiadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344639
3. Query_time: 22.066348 Lock_time: 0.000236 Rows_sent: 10 Rows_examined: 3325313

use eiadwikip1;
SET timestamp=1485955424;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

1. Time: 170201 8:23:52
2. User@Host: citypediaadmcitypediaadm @ clwp001.city.a.ottawa.ca 10.140.12.238 Id: 344642
3. Query_time: 13.027229 Lock_time: 0.000440 Rows_sent: 10 Rows_examined: 2035464

use citypediap1;
SET timestamp=1485955432;
select `recipientObjectId` as `login`,
`pointsBalance` as `score`
from `tiki_object_scores` tos
where `recipientObjectType`='user'
and tos.`id` = (select max(id) from `tiki_object_scores` where `recipientObjectId` = tos.`recipientObjectId` and `recipientObjectType`='user' group by `recipientObjectId`)
group by `recipientObjectId` order by `score` desc LIMIT 10 OFFSET 0;

This bug has been demonstrated on show2.tiki.org

Please demonstrate your bug on show2.tiki.org

This bug has been demonstrated on show.tikiwiki.org

Please demonstrate your bug on show.tikiwiki.org