I have 2 queries in wordpress that slow significantly my website. the first one is
global $wpdb;
$tbl = $wpdb->prefix . "my_tournament_matches_events";
$result = $wpdb->get_var("SELECT count(*) FROM $tbl WHERE match_id = '$match' AND player_id = '$player' AND event_id = 'app'");
if($result) { return 'checked="checked"';
The problem is that it checks for every single player (0.33sec) if he attended the game, which means 20 queries for each team (6.6sec and for both teams=13,2sec). I wonder if i could make it with only two queries (One for each team).
The second query is
$output.="<td colspan='2'>".my_fetch_data_from_id($homeplayer->player_id,'data_value')."</td>";
foreach($events as $event){
$evcount = $wpdb->get_results("SELECT count(*) as total_events
FROM $table2
WHERE match_id='$mid'
AND player_id='$homeplayer->player_id'
AND event_id='$event->id'" );
$total= $evcount[0]->total_events;
$output.="<td><input type='text' name=hm-$homeplayer->player_id-$event->id value='$total'/></td>";
}
In the second query it checks for each player, but since i have available about 11 events, that means, it executes 11events*20players*2teams=440 queries. Is it possible to alter the code to reduse number of queries and time of them? Average time of each of the second query is about 0,3sec, so every improvement will be noticable
I suggest adding two indexes in the database. (and ofc. checking if they are present)
ALTER TABLE my_tournament_matches_events
ADD INDEX index1 (`player_id`, `match_id`, `event_id`);
ALTER TABLE total_events
ADD INDEX index2 (`player_id`, `match_id`, `event_id`);
This should speed things up, considerably. 0.33s is a slow query response time.
To sum it up:
LIMIT 1 to the queries this will make sure that if the query finds a result it doesn't continue looking for others;* in count but only count on the required fields;For readability, consistency and some errors you can change the single and double quotes:
$output.='<td colspan="2">'.my_fetch_data_from_id( $homeplayer->player_id, 'data_value' ).'</td>';
$output.='<td><input type="text" name="hm-'.$homeplayer->player_id-$event->id.'"value="'.$total.'"/></td>';
// Seems odd here otherwise ^