Closed rr- closed 11 years ago
Tests proved that multiple JOIN
s were actually slower than grouping data client-side (1.01s vs 0.97s). Attaching code used for tests.
MediaGenreDistribution.php
:
public static function fromUser($user, $media)
{
$dist = new self();
R::begin();
$query = 'SELECT mg.mal_id AS mg_mal_id, mg.name AS mg_name, mg.id AS mg_id, m.*, um.*, m.id AS media_id' .
' FROM mediagenre mg' .
' INNER JOIN media m ON mg.media_id = m.id' .
' INNER JOIN usermedia um ON m.mal_id = um.mal_id AND m.media = um.media' .
' WHERE um.user_id = ? AND um.media = ?' .
' AND status != ?';
$data = R::getAll($query, [$user->id, $media, UserListStatus::Planned]);
R::rollback();
$map = [];
foreach ($data as $row)
{
$key = $row['media_id'];
if (!isset($map[$key]))
{
$entry = new Model_MixedUserMedia($row);
$map[$key] = $entry;
$map[$key]->genres = [];
}
$g = new StdClass;
$g->id = $row['mg_id'];
$g->mal_id = $row['mg_mal_id'];
$g->name = $row['mg_name'];
$map[$key]->genres []= $g;
}
foreach ($map as $entry)
{
$dist->addEntry($entry);
}
$dist->finalize();
return $dist;
}
UserControllerFavoritesModule.php
:
#$favGenres = MediaGenreDistribution::fromEntries($listNonPlanned);
$favGenres = MediaGenreDistribution::fromUser($viewContext->user, $viewContext->media);
Right now all operations are done client-side (in terms of database connections). Constructing score distribution from 90000 entries means retrieval of 90000 entries from database and grouping them locally in PHP code. Consider doing selects like
SELECT COUNT(score) FROM usermedia WHERE ... GROUP BY score
insideDistribution
classes. In order to achieve this, filters and perhaps sorters too should be moved to their own classes that can convert themselves to SQL expressions. After thatgetMixedUserMedia
should be able to accept sorters and filters as parameters.