Django

Code

Ticket #3575: 3575-mysql-performance.txt

File 3575-mysql-performance.txt, 1.9 kB (added by cgrady, 8 months ago)
Line 
1 mysql> 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 +---------------------+
7 1 row in set (0.89 sec)
8
9 mysql> 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 +---------------------+
15 1 row in set (2.09 sec)
16
17 mysql> create index game_lower_white_player_idx on game (white_player);
18 Query OK, 301279 rows affected (7.93 sec)
19 Records: 301279  Duplicates: 0  Warnings: 0
20
21 mysql> create index game_lower_black_player_idx on game (black_player);                                                                                                                                                                                                       
22 Query OK, 301279 rows affected (12.96 sec)
23 Records: 301279  Duplicates: 0  Warnings: 0
24
25 mysql> 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 +---------------------+
31 1 row in set (0.16 sec)
32
33 mysql> 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 +---------------------+
39 1 row in set (1.83 sec)