[B! join] yassã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

yass id:yass

joinã«é–¢ã™ã‚‹yassã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (36)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

Demystifying JOIN Algorithms
Joins are an important class of operations in databases and data pipelines. There are many kinds of joins in SQL databases, but this post will focus only on the INNER JOIN. 1 It is possible to make the JOIN between two large tables very efficient if the result is going to be small. That being a common scenario in practice, makes JOINs an interesting topic of research. A naive JOIN algorithm can be
yass 2019/02/02
sql

join
ãƒªãƒ³ã‚¯
ãƒã‚°ã‚¤ãƒ³ - ã¯ã¦ãª
ãƒ‘ã‚¹ãƒ¯ãƒ¼ãƒ‰ã‚’å¿˜ã‚ŒãŸæ–¹ã¯ãƒ‘ã‚¹ãƒ¯ãƒ¼ãƒ‰ã®å†è¨å®šã‚’è¡Œã£ã¦ãã ã•ã„ã€‚ åˆã‚ã¦ã®æ–¹ã¯ã¯ã¦ãªIDç™»éŒ² (ç„¡æ–™) ã—ã¦ãã ã•ã„ã€‚ ã†ã¾ããƒã‚°ã‚¤ãƒ³ã§ããªã„æ–¹ã¯ãŠå•ã„åˆã‚ã›ã‚’ã”è¦§ã„ãŸã ãã€Cookieã®è¨å®šã‚’ã”ç¢ºèªãã ã•ã„ã€‚
yass 2017/08/23
redash

MySQL

BigQuery

join

python
ãƒªãƒ³ã‚¯
GitHub - vi/fdlinecombine: Read multiple fds and print data to stdout linewise.
yass 2017/01/06
cui

join

linux
ãƒªãƒ³ã‚¯
ã‚¢ã‚·ã‚¹ãƒˆã®è¦–ç‚¹ã€Œã‚¢ã‚¯ã‚»ã‚¹ãƒ»ãƒ‘ã‚¹ã‹ã‚‰è¦‹ã‚‹RDBMSã®é•ã„ã€ | ã‚¢ã‚·ã‚¹ãƒˆ
å•†ç”¨RDBMSã®ãƒ‡ãƒ•ã‚¡ã‚¯ãƒˆãƒ»ã‚¹ã‚¿ãƒ³ãƒ€ãƒ¼ãƒ‰ã§ã‚ã‚‹ã€ŒOracle Databaseã€ã€ã‚ªãƒ¼ãƒ—ãƒ³ã‚½ãƒ¼ã‚¹RDBMSã®ä¸¡é›„ã§ã‚ã‚‹ã€ŒPostgreSQLã€ã€ã€ŒMySQLã€ã«ã¯ã€ãã‚Œãžã‚Œã®RDBMSã®å€‹æ€§ã‚’æ´»ã‹ã›ã‚‹é©ç”¨é ˜åŸŸãŒã‚ã‚Šã¾ã™ã€‚æœ¬ç¨¿ã§ã¯ã€RDBMSã®ã€Œæ€§èƒ½è¦ä»¶ã€ã«æ·±ãé–¢ä¿‚ã™ã‚‹ã€Œã‚¢ã‚¯ã‚»ã‚¹ãƒ»ãƒ‘ã‚¹ã€ã‚’é¡Œæã«ã€ã€ŒOracle Databaseã€ã€ã€ŒPostgreSQLã€ã€ã€ŒMySQLã€ã®ç‰¹é•·ã‚’æ•´ç†ã—ã€é©æé©æ‰€ã§æ´»ç”¨ã™ã‚‹ãƒ’ãƒ³ãƒˆã‚’æŽ¢ã‚Šã¾ã™ã€‚ ã‚¢ã‚·ã‚¹ãƒˆã§ã¯1987å¹´ã«å–ã‚Šæ‰±ã„ã‚’é–‹å§‹ã—ãŸã€ŒOracle Databaseã€ã«åŠ ãˆã€2009å¹´10æœˆã‚ˆã‚Šã€ŒPostgreSQLï¼ˆãƒã‚¹ãƒˆã‚°ãƒ¬ã‚¹ã‚ãƒ¥ãƒ¼ã‚¨ãƒ«ï¼‰ã€ã€2011å¹´10æœˆã«ã¯PostgreSQLã‚’ãƒ™ãƒ¼ã‚¹ã¨ã—Oracle Databaseã¨ã®äº’æ›æ€§æ©Ÿèƒ½ã‚’æœ‰ã™ã‚‹ã€ŒPostgres Plusï¼ˆãƒã‚¹ãƒˆã‚°ãƒ¬ã‚¹ãƒ—ãƒ©ã‚¹ï¼‰ã€ã€Webã‚µãƒ¼ãƒ“ã‚¹ã®ã‚·ã‚¹ãƒ†ãƒ ã«ã¦æŽ¡ç”¨ã•ã‚Œã¦ã„ã‚‹RDBMSã¨ã—ã¦ã¯
yass 2016/12/18
database

oracle

PostgreSQL

MySQL

index

join
ãƒªãƒ³ã‚¯
MySQLã®GROUP_CONCATãŒã‚¢ãƒ„ã„ - Qiita
http://qiita.com/advent-calendar/2015/gaiax åˆã‚ã¾ã—ã¦ã€GaiaxAdventCalender 4æ—¥ç›®æ‹…å½“ã®æŠ€è¡“é–‹ç™ºéƒ¨ã®é‡‘ç”°ã¨ç”³ã—ã¾ã™ã€‚ æ—¢ã«ç†±ã„è¨˜äº‹ã°ã‹ã‚Šãªã®ã§å¾Œç¶šã®äººãŸã¡ã®ãƒãƒ¼ãƒ‰ãƒ«ã‚’ä¸‹ã’ã‚‹ç‚ºã«å°ãƒã‚¿ã§è¡ŒããŸã„ã¨æ€ã„ã¾ã™ã€‚ GROUP_CONCATã¨ã„ã†é–¢æ•°ãŒMySQLã«ã‚ã‚‹ã‚“ã§ã™ãŒãã‚ŒãŒã‚¢ãƒ„ã„ã®ã§ã¿ã‚“ãªã«æ•™ãˆãŸã„ã¨ã„ã†è¨˜äº‹ã§ã™ã€‚ 1å¯¾å¤šã®é–¢ä¿‚ã®ã¨ã æ™®é€šã«ãƒ†ãƒ¼ãƒ–ãƒ«è¨è¨ˆã—ã¦ã‚‹ã¨1å¯¾å¤šã®é–¢ä¿‚(has many)ã«ãªã‚‹ãƒªãƒ¬ãƒ¼ã‚·ãƒ§ãƒ³ã‚·ãƒƒãƒ—ã‚’ãƒ†ãƒ¼ãƒ–ãƒ«é–“ã§å¼µã‚‹ã“ã¨ãŒå¤šã„ã§ã™ã‚ˆãã€‚ ã¾ãã“ã‚“ãªæ„Ÿã˜ã§ã€‚ ã–ã£ãã‚Šã¨è¨˜äº‹ã«å¯¾ã—ã¦è¤‡æ•°ã®ã‚¿ã‚°ãŒè¨å®šã§ãã‚‹ã‚ˆã†ãªä½œã‚Šã‚’æƒ³å®šã—ã¦ã¿ã¾ã™ã€‚ Entryã‹ã‚‰å‡ºãŸç·šãŒTagã¯è¤‡æ•°ã«æžåˆ†ã‹ã‚Œã—ã¦ã„ã‚‹ã®ãŒhas manyã®é–¢ä¿‚ã§ã™ã€‚ ã§ã€ã“ã‚“ãªãƒ‡ãƒ¼ã‚¿ãŒå…¥ã£ã¦ã¾ã™ã€‚ å–å¾—ã™ã‚‹ã¨ã è¨˜äº‹ã¨ã€ãã‚Œã«ä»˜éšã—ãŸã‚¿ã‚°ã®ä¸€è¦§ã‚’å–å¾—ã—ãŸã„ã¨ãã¯å½“
yass 2016/11/03
mysql

join

sql
ãƒªãƒ³ã‚¯
RDB - å®Ÿä¾‹ã§å¦ã¶ã€JOIN (NLJ) ãŒé…ããªã‚‹ç†å±ˆã¨å¯¾å‡¦æ³• - Qiita
"Nested Loop Joinã—ã‹å–ã‚Šä¸Šã’ã¦ç„¡ã„ã®ã«ã‚¿ã‚¤ãƒˆãƒ«ãŒå¤§ãã™ãŽãªã„ã‹" ã¨æŒ‡æ‘˜ã‚’é ‚ã„ãŸã®ã§ã€ã‚¿ã‚¤ãƒˆãƒ«ã‚’ä¿®æ£ã—ã¾ã—ãŸã€‚Merge Joinã¨Hash Joinã®ã“ã¨ã¯ã¾ãŸä»Šåº¦æ›¸ã“ã†ã¨æ€ã„ã¾ã™ã€‚ ã€ŒJOINã¯é…ã„ã€ã¨ã‚ˆãè¨€ã‚ã‚Œã¾ã™ã€‚ç‰¹ã«RDBã‚’ä½¿ã„å§‹ã‚ã¦é–“ãŒãªã„å†…ã«ãã†ã„ã†è¨€èª¬ã«è§¦ã‚ŒãŸçµæžœã€ŒJOIN=æ‚ªã€ã¨ã„ã†èªè˜ã§å›ºå®šåŒ–ã•ã‚Œã¦ã—ã¾ã£ã¦ã„ã‚‹äººã‚‚å¤šã„ã‚ˆã†ã«æ„Ÿã˜ã¦ã„ã¾ã™ã€‚ ãŸã—ã‹ã«ã€JOINã‚’å«ã‚€ã‚ˆã†ãªSELECTæ–‡ã¯ã€å«ã¾ãªã„ã‚‚ã®ã«æ¯”ã¹ã¦é‡ãŸããªã‚‹å‚¾å‘ãŒã‚ã‚‹ã“ã¨ã¯äº‹å®Ÿã§ã™ã€‚ã¾ãŸã€æœ¬è³ªçš„ã«å•ã„åˆã‚ã›ãŸã„å†…å®¹ãŒè¤‡é›‘ã§ã€å¯¾å‡¦ã™ã‚‹ã“ã¨ãŒé›£ã—ã„ã‚‚ã®ã‚‚å˜åœ¨ã—ã¾ã™ã€‚ã—ã‹ã—ã€RDBã®ä¸ã§ä¸€ä½“ã©ã†ã„ã†ã“ã¨ãŒèµ·ãã¦ã„ã‚‹ã®ã‹ã‚’çŸ¥ã‚Šã€ãã‚Œã«åŸºã„ã¦å¯¾å‡¦ã™ã‚Œã°é«˜é€ŸåŒ–ã§ãã‚‹ã“ã¨ã‚‚å°‘ãªããªã„ã¨è€ƒãˆã¦ã„ã¾ã™ã€‚ æœ¬ç¨¿ã§ã¯ã€JOINã®å†…éƒ¨å‹•ä½œã‚’è§£èª¬ã—ãŸä¸Šã§ã€Webã‚µãƒ¼ãƒ“ã‚¹ã‚’ä½œã£ã¦ã„ã‚‹ã¨ã‚ˆãå‡ºã¦ãã‚‹JOIN SQLã‚’ä¾‹é¡Œã«
yass 2015/07/12
sql

join

database

nested loop join

index
ãƒªãƒ³ã‚¯
æ·±ã„è¦ªåé–¢ä¿‚ã®ãƒ†ãƒ¼ãƒ–ãƒ«è¨è¨ˆ
ã¡ã‚ƒã‚“ã¨ã—ãŸ C# ãƒ—ãƒã‚°ãƒ©ãƒ ã‚’æ›¸ã‘ã‚‹ã‚ˆã†ã«ãªã‚‹å®Ÿè·µçš„ãªæ–¹æ³•ï½ž Visual Studio ã‚’ä½¿ã£ãŸ é«˜å“è³ªãƒ»ä½Žã‚³ã‚¹ãƒˆãƒ»ä¿å®ˆæ€§ã®é«˜ã„é–‹ç™º
yass 2015/02/14
SQL

join

foreign key

index
ãƒªãƒ³ã‚¯
Whereç‹™ã„ã®ã‚ãƒ¼ã€order byç‹™ã„ã®ã‚ãƒ¼
Kubernetesã§ã®æ€§èƒ½è§£æž ï½žãªã‚“ã¨ãªãé…ã„ã‹ã‚‰ã®è„±å´ï½žï¼ˆKubernetes Meetup Tokyo #33 ç™ºè¡¨è³‡æ–™ï¼‰
yass 2014/08/29
MySQL

sql

index

join

explain

sort
ãƒªãƒ³ã‚¯
ä¸¦åˆ—ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚·ã‚¹ãƒ†ãƒ ã®æ¦‚å¿µã¨åŽŸç†
2014/01/30 ç‘æ³¢å¤§å¦æƒ…å ±ã‚·ã‚¹ãƒ†ãƒ ç‰¹åˆ¥è¬›ç¾©Dã®è¬›ç¾©è³‡æ–™ã§ã™ã€‚ joiné–¢ä¿‚ã¯NAISTæ™‚ä»£ã®å®®å´Žå…ˆç”Ÿã®ãƒ‡ãƒ¼ã‚¿å·¥å¦â…¡ã®å†…å®¹ã‚’å‚è€ƒã«ã—ã¦ã¾ã™ã€‚ animationæœ‰åŠ¹ãªãƒ“ãƒ‡ã‚ªã¯ã“ã¡ã‚‰ https://vimeo.com/85598907
yass 2014/02/02
MapReduce

database

join

sort

merge sort

partitioning
ãƒªãƒ³ã‚¯
Choose the best distribution style - Amazon Redshift
Amazon Redshift will no longer support the creation of new Python UDFs starting November 1, 2025. If you would like to use Python UDFs, create the UDFs prior to that date. Existing Python UDFs will continue to function as normal. For more information, see the blog post . Choose the best distribution style When you run a query, the query optimizer redistributes the rows to the compute nodes as need
yass 2013/11/04
" If you frequently join a table, specify the join column as the distribution key. "

redshift

join

index
ãƒªãƒ³ã‚¯
Optimizing Queries | Server | MariaDB Documentation
Optimizing QueriesOptimize queries for peak performance. This section provides techniques for writing efficient SQL, understanding query execution plans, and leveraging indexes effectively to speed up your queries.
yass 2013/10/23
" Different query optimizations and how you can use and tune them to get better performance. "

mariadb

mysql

sql

join
ãƒªãƒ³ã‚¯
é•·å¹´ã®è°è«–ã«çµ‚æ¢ç¬¦ -- MySQLã€MariaDBã€PostgreSQLã®ã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶/ã‚¨ã‚¯ã‚¼ã‚ãƒ¥ãƒ¼ã‚¿æ¯”è¼ƒ - interdbâ€™s blog
https://mariadb.com/kb/en/optimizer-switch/ã«ã‚ã‚‹ã‚ˆã†ã«ã€MariaDBã®ã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶ã¯ã‹ãªã‚Šæ”¹è‰¯ã•ã‚Œã¦ã„ã‚‹ã€‚ ã§ã¯ã€MariaDBã®ã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶/ã‚¨ã‚¯ã‚¼ã‚ãƒ¥ãƒ¼ã‚¿ã¯ã©ã®ç¨‹åº¦å„ªç§€ã‹ã€ï¼”ã¤ã®SELECTæ–‡ã®å®Ÿè¡Œã‚’é€šã—ã¦MySQLã¨(ã¤ã„ã§ã«PostgreSQLã¨)æ¯”è¼ƒã—ã¦ã¿ã‚‹ã€‚ (2014.12.3è¿½è¨˜ï¼šã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶ã«ã¤ã„ã¦ã¯çœç•¥ã—ã¦ã¾ã™ãŒã€ã“ã‚“ãªæœ¬ãŒã§ã¾ã™ã€‚) çµè«–ã‚’å…ˆã«ã„ãˆã°ã€ŒMySQLã¯æ¤œç´¢ãŒé€Ÿã„ã€ã¨ã„ã†ã®ã¯éƒ½å¸‚ä¼èª¬ã€‚MariaDBã¯ãŒã‚“ã°ã£ã¦ã‚‹ã‘ã©PostgreSQLã«ã¯ã¾ã ã¾ã åŠã°ãšã€‚ ï¼Šå¿µã®ãŸã‚ã€‚ã“ã‚Œã¯ãƒ™ãƒ³ãƒãƒžãƒ¼ã‚¯ã˜ã‚ƒãªã„ã‚ˆã€ã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶/ã‚¨ã‚¯ã‚¼ã‚ãƒ¥ãƒ¼ã‚¿ã®æ©Ÿèƒ½æ¯”è¼ƒã§ã™ã€‚ è‡ªåˆ†ã§å†ç¢ºèªã—ãŸã„å ´åˆã¯ã“ã¡ã‚‰ã«ã‚¹ã‚¯ãƒªãƒ—ãƒˆç¾¤ã¨å®Ÿé¨“ã®ã‚„ã‚Šæ–¹ã‚’ç°¡å˜ã«æ›¸ã„ãŸã®ã§å‚ç…§ã®ã“ã¨ã€‚ èª¿æŸ»ç’°å¢ƒ åŒä¸€ãƒžã‚·ãƒ³ã«MySQL5.6.14ã€MariaDB10.0.4ã€
yass 2013/10/22
" å€‹äººçš„ã«ã¯å…¨èˆ¬çš„ãªæ€§èƒ½ã¨é ‘å¥æ€§ã‹ã‚‰PostgreSQLã‚’è–¦ã‚ã‚‹ã‘ã‚Œã©ã‚‚ã€ã›ã„ãœã„ï¼’ã€ï¼“å€‹ã®ãƒ†ãƒ¼ãƒ–ãƒ«JOINã‚’ã‚¹ãƒ¬ãƒƒãƒ‰ãƒ—ãƒ¼ãƒ«ã§é«˜é€Ÿã«æŒãã®ã§ã‚ã‚Œã°ã€MariaDBã§ã‚‚ã„ã„ã‚“ã˜ã‚ƒãªã„ã‹ã¨æ€ã†ã€‚"

mysql

mariadb

PostgreSQL

sql

join
ãƒªãƒ³ã‚¯
HTTP 404 ãƒ•ã‚¡ã‚¤ãƒ«æœªæ¤œå‡º - SCIENCE TOKYO OCW
ãƒšãƒ¼ã‚¸ãŒè¦‹ã¤ã‹ã‚Šã¾ã›ã‚“ æ¤œç´¢ä¸ã®ãƒšãƒ¼ã‚¸ã¯ã€å‰Šé™¤ã•ã‚ŒãŸã€åå‰ãŒå¤‰æ›´ã•ã‚ŒãŸã€ã¾ãŸã¯ç¾åœ¨åˆ©ç”¨ã§ããªã„å¯èƒ½æ€§ãŒã‚ã‚Šã¾ã™ã€‚ â€»éŽå¹´åº¦è¬›ç¾©ã®å ´åˆã¯æ–°å¹´åº¦è¬›ç¾©ã‚’å…¬é–‹ã—ãŸãŸã‚ã«å‰Šé™¤ã•ã‚ŒãŸå ´åˆãŒã‚ã‚Šã¾ã™ã€‚ æ¬¡ã®ã“ã¨ã‚’è©¦ã—ã¦ãã ã•ã„ï¼š ãƒ»ã‚¢ãƒ‰ãƒ¬ã‚¹ãƒãƒ¼ã«ãƒšãƒ¼ã‚¸ã‚¢ãƒ‰ãƒ¬ã‚¹ã‚’å…¥åŠ›ã—ãŸå ´åˆã¯ã€ãƒšãƒ¼ã‚¸ã‚¢ãƒ‰ãƒ¬ã‚¹ã‚’æ£ã—ãå…¥åŠ›ã—ãŸã‹ã©ã†ã‹ã‚’ç¢ºèªã—ã¦ãã ã•ã„ã€‚ ãƒ»[ãƒ›ãƒ¼ãƒ ãƒšãƒ¼ã‚¸]ã‚’é–‹ã„ã¦ã‹ã‚‰ã€è¡¨ç¤ºã™ã‚‹æƒ…å ±ã¸ã®ãƒªãƒ³ã‚¯ã‚’æŽ¢ã—ã¦ãã ã•ã„ã€‚ ãƒ»åˆ¥ã®ãƒªãƒ³ã‚¯å…ˆã‚’è¡¨ç¤ºã™ã‚‹ã«ã¯ã€[æˆ»ã‚‹]ã‚’ã‚¯ãƒªãƒƒã‚¯ã—ã¦ãã ã•ã„ã€‚ ãƒ»å‚ç…§ã—ãŸã„ã‚ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’ç”¨ã„ã¦ã€SCIENCE TOKYO OCWã®æ¤œç´¢ã‚’ã—ã¦ãã ã•ã„ã€‚
yass 2013/10/21
database

join
ãƒªãƒ³ã‚¯
MySQL: Welcome
yass 2013/10/21
" This buffer is used only when the join is of type ALL or index (in other words, when no possible keys can be used). / The larger the value of join_buffer_size, the fewer the scans of t3. "

mysql

join

memory
ãƒªãƒ³ã‚¯
MySQLãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°è™Žã®å·»ï¼ã‚½ãƒ¼ãƒˆã«é–¢é€£ã™ã‚‹ãƒˆãƒ”ãƒƒã‚¯ã¨ã‚¯ã‚¨ãƒªã®æ›¸ãæ›ãˆ
EnterpriseZineï¼ˆã‚¨ãƒ³ã‚¿ãƒ¼ãƒ—ãƒ©ã‚¤ã‚ºã‚¸ãƒ³ï¼‰ç·¨é›†éƒ¨ã§ã¯ã€æƒ…å ±ã‚·ã‚¹ãƒ†ãƒ æ‹…å½“ã€ã‚»ã‚ãƒ¥ãƒªãƒ†ã‚£æ‹…å½“ã®æ–¹ã€…å‘ã‘ã«ã€EnterpriseZine Dayã€Security Online Dayã€DataTechã¨ã„ã†ã€3ã¤ã®ã‚¤ãƒ™ãƒ³ãƒˆã‚’é–‹å‚¬ã—ã¦ãŠã‚Šã¾ã™ã€‚ãã‚Œãžã‚Œç·¨é›†éƒ¨ç‹¬è‡ªã®åˆ‡ã‚Šå£ã§ã€æ¥ç•Œãƒˆãƒ¬ãƒ³ãƒ‰ã‚„æœ€æ–°äº‹ä¾‹ã‚’ç¶²ç¾…ã€‚æœ€æ–°ã®å‹•å‘ã‚’çŸ¥ã‚‹ã“ã¨ãŒã§ãã‚‹å ´ã¨ã—ã¦ã€å¥½è©•ã‚’å¾—ã¦ã„ã¾ã™ã€‚
yass 2013/10/20
" MySQLã¯åŸºæœ¬çš„ã«æ¬¡ã®é¸æŠžè‚¢ã—ã‹ãªã„ã€‚é§†å‹•è¡¨ã§ã‚½ãƒ¼ãƒˆã—ã¦ã‹ã‚‰JOINã™ã‚‹ã‹ã€JOINã—ã¦ã‹ã‚‰å…¨ä½“ã‚’ã‚½ãƒ¼ãƒˆã™ã‚‹ã‹ã€‚"

mysql

index

sort

join
ãƒªãƒ³ã‚¯
Use of join buffer is now visible in EXPLAIN
UPDATE: * s/Using join cache/Using join buffer/, changed to show the final variants of EXPLAIN output as described here * s/join_buff_size/join_buffer_size/ Starting from 5.1.18, EXPLAIN output may show â€œUsing join cacheâ€œ, like in this example: mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar'; +----+-------------+-------+-------+-...-+--------------------------------+ | id
yass 2013/10/19
" It is apparent that the second and the third scans of table tbl3 bring no new information and can be removed. We do not have to re-scan tbl3 for any row combination from tables tbl1, tbl2. Instead, we could accumulate a back of such row combination, and then do one tbl3 scan for all of them. "

mysql

join

memory

nested loop join
ãƒªãƒ³ã‚¯
ï¼»ç¬¬14å›žï¼½ãƒã‚¹ãƒ†ãƒƒãƒ‰ãƒ«ãƒ¼ãƒ—ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã®ãƒ‡ã‚£ã‚¹ã‚¯ãƒ»ã‚¢ã‚¯ã‚»ã‚¹å›žæ•°ã‚’æ¦‚ç®—
å‰å›žï¼Œãƒã‚¹ãƒ†ãƒƒãƒ‰ãƒ«ãƒ¼ãƒ—ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã®äºŒã¤ã®å‡¦ç†æ‰‹ç¶šãã‚’èª¬æ˜Žã—ã¾ã—ãŸã€‚ä»Šå›žã¯ï¼Œãã®äºŒã¤ã®å‡¦ç†æ‰‹ç¶šãã®ãƒ‡ã‚£ã‚¹ã‚¯ãƒ»ã‚¢ã‚¯ã‚»ã‚¹å›žæ•°ã‚’è¦‹ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ãƒ‡ã‚£ã‚¹ã‚¯ãƒ»ã‚¢ã‚¯ã‚»ã‚¹ã¯ãƒ–ãƒãƒƒã‚¯ã®èªã¿å–ã‚Šå›žæ•°ã§è€ƒãˆã‚‹å¿…è¦ãŒã‚ã‚Šã¾ã™ã€‚ ã“ã“ã§ã¯è©±ã‚’åˆ†ã‹ã‚Šã‚„ã™ãã™ã‚‹ãŸã‚ï¼Œï¼»é¡§å®¢ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ï¼ˆè¡¨1ï¼‰ã®30ãƒ¬ã‚³ãƒ¼ãƒ‰ã¯10ãƒ–ãƒãƒƒã‚¯ï¼ˆ1ãƒ–ãƒãƒƒã‚¯ã«3ãƒ¬ã‚³ãƒ¼ãƒ‰ï¼‰ã€‚ã“ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«ã¯ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ãŒä»˜ã„ã¦ã„ã¾ã™ã®ã§ï¼Œãã‚ŒãŒ5ãƒ–ãƒãƒƒã‚¯ã€‚å…¨éƒ¨ã§15ãƒ–ãƒãƒƒã‚¯ã ã¨ã—ã¾ã™ã€‚ï¼»è²©å£²ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ï¼ˆè¡¨2ï¼‰ã®1000ãƒ¬ã‚³ãƒ¼ãƒ‰ã¯ï¼Œ250ãƒ–ãƒãƒƒã‚¯ï¼ˆ1ãƒ–ãƒãƒƒã‚¯ã«4ãƒ¬ã‚³ãƒ¼ãƒ‰ï¼‰ã«æ ¼ç´ã•ã‚Œã¦ã„ã‚‹ã¨ã—ã¾ã™ã€‚
yass 2013/10/17
database

join
ãƒªãƒ³ã‚¯
ï¼»ç¬¬12å›žï¼½ã‚½ãƒ¼ãƒˆãƒžãƒ¼ã‚¸ã¨ãƒãƒƒã‚·ãƒ¥ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³
SELECT è²©å£²æ—¥ï¼Œé¡§å®¢åï¼Œå•†å“ã‚³ãƒ¼ãƒ‰ï¼Œæ•°é‡ FROM é¡§å®¢ï¼Œè²©å£² WHERE é¡§å®¢.é¡§å®¢ç•ªå· = è²©å£².é¡§å®¢ç•ªå· AND é¡§å®¢.é¡§å®¢å = 'æ¸…æ°´é¦™ç¹”' æ„å‘³ï¼šï¼»é¡§å®¢ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ã®ï¼»é¡§å®¢ç•ªå·ï¼½ã¨ï¼Œï¼»è²©å£²ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ã®ï¼»é¡§å®¢ç•ªå·ï¼½ãŒåŒã˜å€¤ã§ã‚ã‚‹ã¨ã„ã†æ¡ä»¶ã§ï¼»é¡§å®¢ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ã¨ï¼»è²©å£²ï¼½ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’çµåˆã—ï¼Œï¼»é¡§å®¢åï¼½ã®å€¤ãŒã€Œæ¸…æ°´é¦™ç¹”ã€ã§ã‚ã‚‹ï¼Œï¼»è²©å£²æ—¥ï¼½ï¼»é¡§å®¢åï¼½ï¼»å•†å“ã‚³ãƒ¼ãƒ‰ï¼½ï¼»æ•°é‡ï¼½ã‚’æŠ½å‡º ã“ã®SQLæ–‡ã®å‡¦ç†æ‰‹ç¶šãã®èª¬æ˜Žã‚’èª¬æ˜Žã—ã¾ã™ã€‚è€ƒãˆã‚‰ã‚Œã‚‹å‡¦ç†æ‰‹ç¶šãã®ãƒ‘ã‚¿ãƒ¼ãƒ³ã‚’ã™ã¹ã¦ç¤ºã—ãŸã„ã¨ã“ã‚ã§ã™ãŒï¼Œã‚¸ãƒ§ã‚¤ãƒ³ã‚’å®Ÿæ–½ã™ã‚‹å ´åˆï¼Œå‡¦ç†æ‰‹ç¶šãã®ãƒ‘ã‚¿ãƒ¼ãƒ³ã¯ä½•ãƒ‘ã‚¿ãƒ¼ãƒ³ã«ã‚‚ãªã‚Šã¾ã™ã€‚ã¾ãšï¼Œã‚¸ãƒ§ã‚¤ãƒ³ã®å‡¦ç†æ–¹æ³•ã«ã¯è¤‡æ•°ã‚ã‚Šã¾ã™ã€‚ãã—ã¦ï¼Œãã‚Œãžã‚Œã®ã‚¸ãƒ§ã‚¤ãƒ³ã®å‡¦ç†æ–¹æ³•ã”ã¨ã«ï¼Œä½•é€šã‚Šã‚‚ã®æ‰‹ç¶šããŒè€ƒãˆã‚‰ã‚Œã‚‹ã‹ã‚‰ã§ã™ã€‚ ä»£è¡¨çš„ãªã‚¸ãƒ§ã‚¤ãƒ³ã®å‡¦ç†æ–¹æ³•ã«ã¯ï¼Œã€Œãƒã‚¹ãƒ†ãƒƒãƒ‰ãƒ«ãƒ¼ãƒ—ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã€ã€Œã‚½ãƒ¼ãƒˆãƒžãƒ¼ã‚¸ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã€ã€Œãƒãƒƒã‚·ãƒ¥ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã€â€•â€•ãŒ
yass 2013/10/17
database

join

sort

hash
ãƒªãƒ³ã‚¯
æœ€çŸã‹ã¤æœ€é€Ÿã«ã‚¢ã‚¯ã‚»ã‚¹ã™ã‚‹ã€ŒDBé«˜é€ŸåŒ–æŠ€è¡“ã€ï¼ˆå¾Œç·¨ï¼‰
ï¼žï¼žå‰ç·¨ ãƒãƒƒã‚·ãƒ¥åˆ©ç”¨ã§æ¯”è¼ƒã‚’æ¸›ã‚‰ã™ å¾Œç·¨ã§ã¯ã¾ãšï¼Œã‚ªãƒ—ãƒ†ã‚£ãƒžã‚¤ã‚¶ãŒé¸æŠžã™ã‚‹æœ€çŸçµŒè·¯ã‚’ç´¹ä»‹ã™ã‚‹ã€‚ è¤‡æ•°ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’å…±é€šã®ã‚ãƒ¼ã‚’ç”¨ã„ã¦çµåˆã™ã‚‹ã€Œã‚¸ãƒ§ã‚¤ãƒ³ã€ã¯ã„ãã¤ã‹ã®æ–¹å¼ãŒã‚ã‚‹ã€‚ãã®ä¸€ã¤ã§ã‚ã‚‹ãƒãƒƒã‚·ãƒ¥ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³ã¯ï¼Œãƒãƒƒã‚·ãƒ¥é–¢æ•°ã‚’ä½¿ã£ã¦ï¼Œä¸€ã¤ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚ãƒ¼ã‹ã‚‰ãƒãƒƒã‚·ãƒ¥å€¤ã‚’è¨ˆç®—ã™ã‚‹ï¼ˆå›³4ï¼‰ã€‚ã•ã‚‰ã«ï¼Œãƒãƒƒã‚·ãƒ¥å€¤ã¨è©²å½“ã™ã‚‹ãƒ‡ãƒ¼ã‚¿ã§æ§‹æˆã™ã‚‹ã€Œãƒãƒƒã‚·ãƒ¥ãƒ»ãƒ†ãƒ¼ãƒ–ãƒ«ã€ã‚’ä½œæˆã™ã‚‹ã€‚åŒæ§˜ã«äºŒã¤ç›®ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‹ã‚‰ãƒãƒƒã‚·ãƒ¥å€¤ã‚’è¨ˆç®—ã—ï¼Œãã®å€¤ã‚’åŸºã«æ¤œç´¢ã™ã‚‹ãƒãƒƒã‚·ãƒ¥ãƒ»ãƒ†ãƒ¼ãƒ–ãƒ«ã®ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’ç‰¹å®šã™ã‚‹ã€‚ãƒãƒƒã‚·ãƒ¥å€¤ã‚’è¨ˆç®—ã™ã‚‹ã ã‘ã§æ¯”è¼ƒå¯¾è±¡ã®ãƒ‡ãƒ¼ã‚¿ãŒçµžã‚Šè¾¼ã‚ã‚‹ã®ã§ï¼Œã»ã‹ã®ã‚¸ãƒ§ã‚¤ãƒ³æ–¹å¼ã‚ˆã‚Šã‚‚é«˜é€Ÿã«ãªã‚‹å ´åˆãŒã‚ã‚‹ã€‚ãŸã ã—ï¼Œã€Œå„ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’å±•é–‹ã§ãã‚‹ã ã‘ã®ãƒ¡ãƒ¢ãƒªãƒ¼å®¹é‡ãŒå¿…è¦ã«ãªã‚‹ã€‚ãƒ¡ãƒ¢ãƒªãƒ¼ãŒè¶³ã‚Šãªã„ã¨é€†ã«é…ããªã‚‹ã€ï¼ˆä¼Šè—¤å¿ ãƒ†ã‚¯ãƒŽã‚½ãƒªãƒ¥ãƒ¼ã‚·ãƒ§ãƒ³ã‚º ãƒ—ãƒ©ãƒƒãƒˆãƒ•ã‚©ãƒ¼ãƒ æŠ€è¡“éƒ¨ éƒ¨é•·ä»£è¡Œ å®®ç”°æ¦æ°ï¼‰ã¨ã„ã£ãŸæ³¨æ„ç‚¹ãŒã‚ã‚‹ã€‚ å›³4â—ãƒãƒƒã‚·ãƒ¥ãƒ»ã‚¸ãƒ§ã‚¤ãƒ³
yass 2013/10/17
" ã‚¤ãƒ³ãƒ¡ãƒ¢ãƒªãƒ¼DBã¯ã€ãƒ‡ã‚£ã‚¹ã‚¯ãƒ»ã‚¢ã‚¯ã‚»ã‚¹ã‚„ãƒãƒƒãƒ•ã‚¡ç®¡ç†ãŒä¸è¦ã€ãƒ‡ãƒ¼ã‚¿ãŒãƒ¡ãƒ¢ãƒªãƒ¼ä¸Šã«ã‚ã‚‹ã“ã¨ã‚’å‰æã¨ã—ãŸã€ŒT-Treeã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã€ã‚’æŽ¡ç”¨ã€ã¨ã„ã£ãŸå„ªä½æ€§ãŒã‚ã‚‹ "

database

index

join
ãƒªãƒ³ã‚¯
ã‚ªãƒ³ãƒ©ã‚¤ãƒ³ãƒžãƒ‹ãƒ¥ã‚¢ãƒ« ãƒšãƒ¼ã‚¸ç§»è»¢ã®ãŠçŸ¥ã‚‰ã›ï¼šãƒŸãƒ‰ãƒ«ã‚¦ã‚§ã‚¢ï¼šã‚½ãƒ•ãƒˆã‚¦ã‚§ã‚¢ï¼šæ—¥ç«‹
æ—¥ç«‹ã‚ªãƒ¼ãƒ—ãƒ³ãƒŸãƒ‰ãƒ«ã‚¦ã‚§ã‚¢ã¯ã€ãŠå®¢æ§˜ã®æ—¢å˜ã®è²¡ç”£ã‚’ç”Ÿã‹ã—ãªãŒã‚‰ã€é«˜ã„ä¿¡é ¼æ€§ã¨æŸ”è»Ÿæ€§ã€è‡ªå¾‹æ€§ã‚’å‚™ãˆãŸITã‚·ã‚¹ãƒ†ãƒ ã®å®Ÿç¾ã‚’æ”¯ãˆã¦ã„ã¾ã™ã€‚
yass 2013/10/17
database

index

join

hash
ãƒªãƒ³ã‚¯
1 2 æ¬¡ã®ãƒšãƒ¼ã‚¸