Points totaling performance

[nickfreese]

Still adding some tweaks, but I've got a v2 of the totaling function locally running in ~42% the time. One key part is it requires a new index that assumes prize_count entries always begin with delete:no leaderboard:no prize_count: which does feel a little hacky, but that prefix is hard coded in the summergame_commerce module so seems unlikely to change for this particular case.

The index keys off of the PID and off the first 116 characters of the metadata field ( which is the length of that prefix: delete:no leaderboard:no prize_count:):
CREATE INDEX up_to_prize_count ON sg_ledger (pid,metadata(116))

@ejk @eby I'm unsure if an index like this could cause issues for other things, but I imagine the normal metadata index already doesn't perform well. I'm also wondering if we could potentially expand this approach and define specific prefix IDs to create indexes on for other common "metadata LIKE ..." lookups where there is other arbitrary data that prevents the normal index from helping.

I made a v2 of the function and have been calling them both and tracking the execution time. The other major change is summing points and grouping it by game_term, type, and whether its on the leaderboard:

function summergame_get_player_points_v2($pid, $game_term = '', $type = '') {
  $term_filter = FALSE;

  $player_points = [
    'career' => 0,
  ];

  //$query = "SELECT SUM(points) AS total, MIN(timestamp) as min_timestamp,  MAX(timestamp) as max_timestamp, game_term, type FROM sg_ledger WHERE pid = :pid AND metadata NOT LIKE '%leaderboard:no%'";


  $query = "SELECT SUM(points) AS total, MIN(timestamp) as min_timestamp,  MAX(timestamp) as max_timestamp, game_term, type, (CASE WHEN metadata LIKE '%leaderboard:no%' THEN 'leaderboard:no' WHEN metadata NOT LIKE '%leaderboard:no%' THEN '' END) as leader from sg_ledger WHERE pid = :pid";



  $args = [':pid' => $pid];
  if ($game_term != '') {
    $query .= " AND game_term = :game_term";
    $args[':game_term'] = $game_term;
    $term_filter = TRUE;
  }
  if ($type !== '') {
    $query .= " AND type = :type";
    $args[':type'] = $type;
  }
  $query .= " group by game_term, type,  (CASE WHEN metadata LIKE '%leaderboard:no%' THEN 'leaderboard:no' WHEN metadata NOT LIKE '%leaderboard:no%' THEN '' END)";


  $db = \Drupal::database();
  $res = $db->query($query, $args);

  while ($row = $res->fetchAssoc()) {
    if (!$term_filter) {
      $game_term = $row['game_term'];
    }

    $type = $row['type'];

    // if game term is not set, initialize point values
    if (!isset($player_points[$game_term])) {
      $player_points[$game_term] = [
        'balance' => 0,
        'total' => 0,
        'prize_count' => 0,
        'max_timestamp' => (int)$row['min_timestamp'],
        'min_timestamp' => (int)$row['max_timestamp'],
      ];
    }
    else {
      // Check min timestamp for game term
      // (row sort is timestamp DESC so max_timestamp is always set with first row of game term)
      if ($row['min_timestamp'] < $player_points[$game_term]['min_timestamp'] && $row['game_term'] == $game_term) {
        $player_points[$game_term]['min_timestamp'] = $row['min_timestamp'];
      }
    }

    // if type for this game term is not set, initialize point value
    if (!isset($player_points[$game_term]['types'][$type])) {
      $player_points[$game_term]['types'][$type] = 0;
    }

    if ($row['total'] > 0 && strpos($row['leader'], 'leaderboard:no') === FALSE) {
      // Don't count non-leaderboard points in the total

      $player_points[$game_term]['total'] += $row['total'];
      $player_points[$game_term]['balance'] += $row['total'];
      $player_points[$game_term]['types'][$type] += $row['total'];
      $player_points['career'] += $row['total'];
    } else {
      $player_points[$game_term]['balance'] += $row['total'];
    }

    

  }


  /*$sqlLeaderboard = "SELECT SUM(points) as total, game_term, type FROM sg_ledger WHERE pid = :pid and metadata LIKE '%leaderboard:no%'";

  if ($term_filter) {
    $sqlLeaderboard .= " AND game_term = :game_term";
    $args[':game_term'] = $game_term;
  }
  $sqlLeaderboard .= " GROUP BY game_term,type";
  $res = $db->query($sqlLeaderboard , $args);

  while ($row = $res->fetchAssoc()) {

    if (!$term_filter) {
      $game_term = $row['game_term'];
    }
    if (!isset($player_points[$game_term]['balance'])) {
      $player_points[$game_term]['balance'] = 0;
    }
    $player_points[$game_term]['balance'] += $row['total'];

  }*/

  //$regexp = 'prize_count:(-?[0-9]+)';
  $query = $db->select('sg_ledger', 'sg_ledger')
  ->condition('pid', $pid, '=');
  if ($term_filter) {
    $query = $query->condition('game_term', $game_term, '=');
  }
  $query = $query->condition('metadata', 'delete:no leaderboard:no prize_count:%', 'LIKE');
  $query->fields('sg_ledger', ['metadata', 'game_term']);

  $result = $query->execute();

  foreach ($result as $row) {    

    if (!$term_filter) {
      $game_term = $row->game_term;
    }

    if (preg_match('/prize_count:(-?\d+)/', $row->metadata, $matches)) {
      $player_points[$game_term]['prize_count'] += (int)$matches[1];
    }
    else {
      $player_points[$game_term]['prize_count'] += 0;
    }

  }

  // Sort type points
  foreach ($player_points as $game_term => &$details) {
    if (isset($details['types'])) {
      arsort($details['types']);
    }
  }

  // Get old badges
  $params = [':pid' => $pid];
  $game_term_filter = "";
  $new_game_term_filter = "";
  if ($game_term !== "") {
    $game_term_filter = " AND sg_badges.game_term = :game_term ";
    $new_game_term_filter = " AND gt.field_badge_game_term_value = :game_term ";

    $params[':game_term'] = $game_term;
  }

  $res = $db->query("SELECT * FROM sg_players_badges, sg_badges " .
                    "WHERE sg_players_badges.pid = :pid AND sg_players_badges.bid = sg_badges.bid " . $game_term_filter .
                    "ORDER BY sg_players_badges.timestamp ASC", $params);
  while ($badge = $res->fetchAssoc()) {
    $game_term = $badge['game_term'];
    $badge['img'] = '/files/old-sg-images/' . $badge['image'] . '_100.png';
    $player_points[$game_term]['badges'][] = $badge;
  }

  // Get new badges
  $user = User::load(\Drupal::currentUser()->id());
  $play_tester = $user->hasPermission('play test summergame');
  $play_test_term_id = \Drupal::config('summergame.settings')->get('summergame_play_test_term_id');

  $res = $db->query("SELECT gt.entity_id AS bid, gt.field_badge_game_term_value AS game_term, b.bid AS pbid " .
                    "FROM node__field_badge_game_term gt, sg_players_badges b " .
                    "WHERE gt.entity_id = b.bid " .
                    "AND b.pid = :pid " . $new_game_term_filter .
                    "ORDER BY bid ASC", $params);
  while ($badge = $res->fetchAssoc()) {
    $game_term = $badge['game_term'];
    $badge['nid'] = $badge['bid'];
    $node = Node::load($badge['bid']);

    // If badge is in the play tester series and user is not a play tester, skip it
    if (!$play_tester) {
      foreach ($node->field_sg_badge_series_multiple as $badge_series) {
        if ($badge_series->target_id == $play_test_term_id) {
          continue 2;
        }
      }
    }

    $fid = File::load($node->get('field_badge_image')->target_id);
    if ($fid) {
      $badge['img'] = \Drupal::service('file_url_generator')->generateAbsoluteString($fid->getFileUri());
    }
    $badge['title'] = $node->get('title')->value;
    $player_points[$game_term]['badges'][] = $badge;
  }


  return $player_points;
}

[eby]

In regards to the index change, if you already have the change in your personal dev you can do some explains on various queries that are in the codebase that use pid or metadata (or both) and see if the index selection still looks good compared to the old with dev.