Because MySQL uses only one index for each table in one execution, we need to decide using index for join or group by. In fact, GROUP BY is also a time-consuming task beside JOIN. Consequently, the above query takes about 4.74 seconds. With each row in `answer` table, it finds exactly one corresponding row in `user` table based on the primary key then joins the row into `answer` table. ![]() The explain result shows that MySQL chooses `answer` as a base table. | 1 | SIMPLE | u | eq_ref | PRIMARY,UNIQUE_username | PRIMARY | 4 | blog.a.user_id | 1 | NULL |īecause one user may have several answers along with different scores, we need to group answers by `username`. | 1 | SIMPLE | a | index | IDX_userid_score | IDX_userid_score | 8 | NULL | 360207 | Using index Using temporary Using filesort | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | mysql> EXPLAIN select SQL_NO_CACHE u.username, u.firstname, u.lastname, max(a.score) as max_scoreįrom answer as a inner join user as u on a.user_id = u.id Let take a look on the following queries to see how where conditions and forcing index affect to performance. `answera table has a `PRIMARY` on `id` column, one single-column index `IDX_score` on `score` column and one multi-column index `IDX_userid_score` on `user_id` and `score` column.Īssume that we want to get top 5 users who have at least one answer with high scores. | answer | 1 | IDX_userid_score | 2 | score | A | 360207 | NULL | NULL | | BTREE | | | | answer | 1 | IDX_userid_score | 1 | user_id | A | 360207 | NULL | NULL | | BTREE | | | | answer | 1 | IDX_score | 1 | score | A | 20011 | NULL | NULL | | BTREE | | | | answer | 0 | PRIMARY | 1 | id | A | 360207 | NULL | NULL | | BTREE | | | `question` table has a `PRIMARY` on `id` column and an index `IDX_SCORE` on `score` column. | question | 1 | IDX_SCORE | 1 | score | A | 10112 | NULL | NULL | | BTREE | | | | question | 0 | PRIMARY | 1 | id | A | 262924 | NULL | NULL | | BTREE | | | `user` table has a `PRIMARY` key on `id` column and an unique key `UNIQUE_username` on `username` column. ![]() | user | 0 | UNIQUE_username | 1 | username | A | 496708 | NULL | NULL | | BTREE | | | ![]() | user | 0 | PRIMARY | 1 | id | A | 496708 | NULL | NULL | | BTREE | | | | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | `answer` table has 528.066 rows. One answer belongs to one question represented by `question_id` column and one user denoted by `user_id`. One question belongs to one user indicated by `user_id` column and one question can have more than one answer. One user may have serveral questions or answers. Each user is identified by an unique ID in `id` column. Figure 1: The database has three tables: `user`, `question` and `answer`.
0 Comments
Leave a Reply. |