Ticket #3575: 3575-mysql-performance.txt

File 3575-mysql-performance.txt, 1.9 KB (added by cgrady, 7 years ago)
Line 
1mysql> select count(white_player) from game where white_player like 'trainer@chesspark.com/TrainingBot' or black_player like 'trainer@chesspark.com/TrainingBot';
2+---------------------+
3| count(white_player) |
4+---------------------+
5|               11771 |
6+---------------------+
71 row in set (0.89 sec)
8
9mysql> select count(white_player) from game where lower(white_player) = lower('trainer@chesspark.com/TrainingBot') or lower(black_player) = lower('trainer@chesspark.com/TrainingBot');
10+---------------------+
11| count(white_player) |
12+---------------------+
13|               11771 |
14+---------------------+
151 row in set (2.09 sec)
16
17mysql> create index game_lower_white_player_idx on game (white_player);
18Query OK, 301279 rows affected (7.93 sec)
19Records: 301279  Duplicates: 0  Warnings: 0
20
21mysql> create index game_lower_black_player_idx on game (black_player);                                                                                                                                                                                                       
22Query OK, 301279 rows affected (12.96 sec)
23Records: 301279  Duplicates: 0  Warnings: 0
24
25mysql> select count(white_player) from game where white_player like 'trainer@chesspark.com/TrainingBot' or black_player like 'trainer@chesspark.com/TrainingBot';                                                                                       
26+---------------------+
27| count(white_player) |
28+---------------------+
29|               11771 |
30+---------------------+
311 row in set (0.16 sec)
32
33mysql> select count(white_player) from game where lower(white_player) = lower('trainer@chesspark.com/TrainingBot') or lower(black_player) = lower('trainer@chesspark.com/TrainingBot');
34+---------------------+
35| count(white_player) |
36+---------------------+
37|               11771 |
38+---------------------+
391 row in set (1.83 sec)
40
Back to Top