11/4/2023 0 Comments Mysql join select statement![]() 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. But, in the "fog of war" on a Zoom call with several other people watching me live-type SQL, this felt like the right approach.Figure 1: The database has three tables: `user`, `question` and `answer`. ![]() Isn't SQL just thrilling?! I am sure there are good number of ways to accomplish what I demonstrated. Woot woot! As you can see, by merging the CSV data and the friend table in a single SQL query, I was able to pull in the friend.name column in order to augment the existing CSV column data. | 5 | Sandi | Vegan, Multi-Grain Pancakes | > ( SELECT 5 AS id, "Vegan, Multi-Grain Pancakes" AS favoriteMeal ) > ( SELECT 4 AS id, "Bella's Chicken Burrito" AS favoriteMeal ) UNION ALL > ( SELECT 1 AS id, "Pizza" AS favoriteMeal ) UNION ALL Imagine that I had a database table called friend with the following structure: That said, let's paint a picture of what was going on. Given the relatively small size of the CSV payload, what follows just felt like the appropriate level of effort. As such, I couldn't load the CSV file into a "real table". But, I was working with a database in which I had read only permissions. ![]() To be clear, this is not the only way to consume CSV data in a relational database. The CSV file was fairly small so, I ended up using the awesome power of multiple-cursors in SublimeText 3 in order to write a SQL query that converted the CSV data into a derived table that I then consumed in an INNER JOIN in MySQL. Yesterday, when working with some data scientists, I had to merge a few columns from a database table into a CSV (Comma-Separated Values) file that the data scientists had created. I've been writing web-applications on top of relational databases (RDBMS) for a while and, SQL always feels like a luxurious combination of structure and flexibility. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |