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

yass id:yass

sqlã¨indexã«é–¢ã™ã‚‹yassã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (7)

${{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}}

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
ãƒªãƒ³ã‚¯
æ·±ã„è¦ªåé–¢ä¿‚ã®ãƒ†ãƒ¼ãƒ–ãƒ«è¨è¨ˆ
2. è‡ªå·±ç´¹ä»‹ @yuba ïµ æ ªå¼ä¼šç¤¾ã‚¤ãƒ³ã‚¿ãƒ¼ã‚³ãƒ ïµ åž‹ã¨åˆ¶ç´„å¤§å¥½ãäººé–“ ïµ ãƒ–ãƒã‚°ã¨ã‹Qiitaã§æ›¸ã„ã¦ã‚‹è¨˜äº‹ãŒã“ã‚“ãªæ„Ÿã˜ã§ã™ã€‚ ïµ è«–ç†å‰Šé™¤ã¨ä¸€æ„æ€§åˆ¶ç´„ã‚’ä¸¡ç«‹ã•ã›ã‚‹æ–¹æ³•ãƒ»DBè£½å“åˆ¥ â€“ Qiita ïµ ãƒˆãƒ©ãƒ³ã‚¶ã‚¯ã‚·ãƒ§ãƒ³ã‚’ãƒã‚¹ãƒˆã—ãŸã‚‰ã©ã†ãªã‚‹ï¼Ÿ å†…å´ã ã‘ãƒãƒ¼ãƒ«ãƒãƒƒã‚¯ã§ãã‚‹ï¼Ÿ - Qiita ïµ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹æ“ä½œã§ãƒ‡ãƒƒãƒ‰ãƒãƒƒã‚¯ã¯ä¸å¯é¿ â€“ C Sharpens you up ïµ å¤–éƒ¨ã‚ãƒ¼å‚ç…§ã—ã‚ã†ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’é…å»¶åˆ¶ç´„ã§å®Ÿç¾ã™ã‚‹ â€“ C Sharpens you up ïµ SQL Serverã®è¨ˆç®—åˆ—ã‚’ä½¿ã£ã¦ãƒ„ãƒªãƒ¼æ§‹é€ ãƒ‡ãƒ¼ã‚¿ã‚’å®Œå…¨ã«åˆ¶ç´„ä»˜ã‘ã‚‹ â€“ C Sharpens you up ïµ SQLã®ã‚«ãƒ©ãƒ åˆ¶ç´„ã¯ãƒ†ãƒ¼ãƒ–ãƒ«åˆ¶ç´„ã¨ç‰ä¾¡ â€“ C Sharpens you up
yass 2015/02/14
SQL

join

foreign key

index
ãƒªãƒ³ã‚¯
Whereç‹™ã„ã®ã‚ãƒ¼ã€order byç‹™ã„ã®ã‚ãƒ¼
11. I'm yoku0825 â— ã¨ã‚ã‚‹ä¼æ¥ã®DBA â— ã‚ªãƒ©ã‚¯ã‚Œãªã„ â— ãƒã‚¹ã‚°ã‚Œãªã„ â— ãƒžã‚¤ã‚¨ã‚¹ã‚ãƒ¥ãƒ¼ã‚¨ã‚‹ â— å®¶ã«å¸°ã‚‹ã¨ â— å«ã®å¤« â— ã›ãŒã‚Œã®çˆ¶ â— é¦¬é¹¿ã ã‹ã‚‰ã‹ã‚ã„ã„ã‚ã‘ã˜ã‚ƒãªãã¦ã€ã‹ã‚ã„ã„ ã‚¤ãƒ«ã‚«ãŒãŸã¾ãŸã¾ãƒã‚«ã ã£ãŸ 12. ã¯ã˜ã‚ã« â— ã‚µãƒ³ãƒ—ãƒ«ãƒ‡ãƒ¼ã‚¿ã¯ MySQLã®ã‚µãƒ³ãƒ—ãƒ«ãƒ‡ãƒ¼ã‚¿ ãƒ™ãƒ¼ã‚¹(worldãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹)ã‹ã‚‰ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ ã‚¹ã‚’å…¨ã¦å–ã£æ‰•ã£ãŸã‚‚ã®ã§ã™ â— http://dev.mysql.com/doc/index-other.html â— ã‚³ãƒ¼ãƒ‰ã¯githubã«ä¸Šã’ã¦ã‚ã‚Šã¾ã™ â— https://github.com/yoku0825/yapc_2014 â— ã™ã”ãâ€¦ã‚¦ãƒ³ã‚³ãƒ¼ãƒ‰ã§ã™â€¦ 13. ã¯ã˜ã‚ã« â— åŽŸå‰‡ã€MySQLã¯1ã¤ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«ã¤ãåŒæ™‚ã«1 ã¤ã®ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã—ã‹ä½¿ã„ã¾ã›ã‚“ â— Index mergeã¨ã‹ã‚ã‚‹ã‘ã©ã‚¢ãƒ¬ã¯ä¾‹å¤–ã ã—ç‹™ã£ã¦ ã‚„ã£ã¦ã‚‚é€Ÿããªã‚‹
yass 2014/08/29
MySQL

sql

index

join

explain

sort
ãƒªãƒ³ã‚¯
ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚¬ã‚½ãƒ³5ã®å¾©ç¿’ MySQL 5.6 æ–°æ©Ÿèƒ½ç·¨ - SH2ã®æ—¥è¨˜
ã¨ã„ã†ã‚ã‘ã§ã€MyNA(æ—¥æœ¬MySQLãƒ¦ãƒ¼ã‚¶ä¼š)ä¼š 2013å¹´3æœˆã«å‚åŠ ã—ã¦ç™ºè¡¨ã‚’ã—ã¦ãã¾ã—ãŸã€‚ã¨ã¦ã‚‚ãƒªãƒ©ãƒƒã‚¯ã‚¹ã—ã¦è©±ã‚’ã™ã‚‹ã“ã¨ãŒã§ãã¾ã—ãŸã€‚å¸ä¼šé€²è¡Œã®å‚äº•ã•ã‚“ã‚’ã¯ã˜ã‚æ—¥æœ¬MySQLãƒ¦ãƒ¼ã‚¶ä¼šã®ã¿ãªã•ã¾ã€æ—¥æœ¬ã‚ªãƒ©ã‚¯ãƒ«ã®ã¿ãªã•ã¾ã€å½“æ—¥ãŠè¶Šã—ã„ãŸã ã„ãŸã¿ãªã•ã¾ã€ã©ã†ã‚‚ã‚ã‚ŠãŒã¨ã†ã”ã–ã„ã¾ã—ãŸã€‚ ç§ã®ã‚»ãƒƒã‚·ãƒ§ãƒ³ã§ã¯å‰å›žã®ã‚¨ãƒ³ãƒˆãƒªã®ç¶šãã¨ã„ã†ã“ã¨ã§ã€MySQL 5.6ã®æ–°æ©Ÿèƒ½Optimizer Traceã‚’æ´»ç”¨ã—ãªãŒã‚‰æ£æ”»æ³•ã§ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã‚’è¡Œã£ã¦ã„ãã¾ã—ãŸã€‚ã¨ã¯ã„ãˆé€”ä¸ã‹ã‚‰æ£æ”»æ³•ã§ã¯ãªããªã£ã¦ã„ãŸæ°—ã‚‚ã—ã¾ã™ã€‚MySQL 5.6ã§RDBMSã¨ã—ã¦ã®åœŸå°ã¯ã—ã£ã‹ã‚Šã—ã¦ããŸã¨æ€ã„ã¾ã™ã®ã§ã€ä»Šå¾Œã¯é«˜åº¦ãªçµ±è¨ˆæƒ…å ±ã‚’ä½¿ç”¨ã—ãŸSQLå®Ÿè¡Œè¨ˆç”»ã®æœ€é©åŒ–ã¨ã„ã£ãŸã¨ã“ã‚ã«ã‚‚æ©Ÿèƒ½å¼·åŒ–ãŒæ–½ã•ã‚Œã¦ã„ãã®ã§ã¯ãªã„ã‹ã¨æœŸå¾…ã—ã¦ã„ã¾ã™ã€‚ ãƒ—ãƒ¬ã‚¼ãƒ³ãƒ†ãƒ¼ã‚·ãƒ§ãƒ³è³‡æ–™ (PDF) EXPLAINã¨Optimizer Traceã®å‡ºåŠ›çµæžœ ãƒ—
yass 2013/04/21
mysql

explain

SQL

index

toread
ãƒªãƒ³ã‚¯
SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...
A site explaining SQL indexing to developersâ€”no crap about administration. SQL indexing is the most effective tuning methodâ€”yet it is often neglected during development. Use The Index, Luke explains SQL indexing from grounds up and doesnâ€™t stop at ORM tools like Hibernate. Use The Index, Luke is the free web-edition of my book SQL Performance Explained (from Ð„9.95). Be sure to subscribe my free ne
yass 2012/12/18
sql

index

toread
ãƒªãƒ³ã‚¯
SQLã®ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã«ã¤ã„ã¦è§£èª¬ã—ãŸæ´‹æ›¸ãŒè‰¯ã‹ã£ãŸä»¶ã€‚PDFç‰ˆã‚‚ã‚ã‚‹ã‚ˆï¼ˆ2012/10/22ã¾ã§ã®å‰²å¼•è³¼å…¥ã‚¯ãƒ¼ãƒãƒ³æœ‰ï¼‰ - ã“ã®ãƒ–ãƒã‚°ã¯URLãŒå¤‰æ›´ã«ãªã‚Šã¾ã—ãŸ
æœ€è¿‘èªã¿çµ‚ãˆãŸSQL PERFORMANCE EXPLAINEDã¨ã„ã†æ´‹æ›¸ãŒã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã«ã¤ã„ã¦è©³ã—ãèª¬æ˜Žã•ã‚Œã¦ã¦ã„ã„æ„Ÿã˜ã§ã—ãŸã€‚åŽŸè‘—ã¯ãƒ‰ã‚¤ãƒ„èªžã§æœ¬äººã«ã‚ˆã‚‹è‹±è¨³ç‰ˆã‚‰ã—ãèª¤å—ã„ãã¤ã‹ç›®ã«ã¤ãã¾ã™ãŒã€ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã«ã¤ã„ã¦ã“ã“ã¾ã§è©³ã—ãèª¬æ˜Žã—ãŸæ›¸ç±ã¯ä»–ã«ä¾‹ãŒã‚ã‚Šã¾ã›ã‚“ã€‚ç§ãŒçŸ¥ã‚‰ãªã„ã ã‘ã‹ã‚‚ã—ã‚Œã¾ã›ã‚“ãŒã€‚ ã©ã†ã„ã£ãŸå†…å®¹ã‹ è¤‡åˆã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã€éƒ¨åˆ†ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã€ã‚¯ãƒ©ã‚¹ã‚¿ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®ä½¿ã†ãƒã‚¤ãƒ³ãƒˆã€LIMITã€ORDER BYã€GROUP BYã§ã®ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®ä½¿ã‚ã‚Œæ–¹ãªã©ãŒè§£èª¬ã•ã‚Œã¦ã¾ã™ã€‚ã¾ãŸã€MySQLã€SQL Serverã€PostgreSQLã€Oracleã®å„å®Ÿè£…ã®é•ã„ãªã©ã‚‚ä¸å¯§ã«ç´¹ä»‹ã•ã‚Œã¦ã„ã¾ã™ã€‚å¯¾è±¡ãƒãƒ¼ã‚¸ãƒ§ãƒ³ãŒæ¯”è¼ƒçš„æ–°ã—ã„ã®ã‚‚ãƒã‚¤ãƒ³ãƒˆã§ã™ãã€‚ è©³ã—ãã¯ç¬¬äºŒç« ã¾ã§èªã‚ã‚‹ãƒ—ãƒ¬ãƒ“ãƒ¥ãƒ¼ç‰ˆã¨ãã®ç›®æ¬¡ã‚’ã”è¦§ãã ã•ã„ã€‚ ãƒšãƒ¼ãƒ‘ãƒ¼ãƒ–ãƒƒã‚¯ç‰ˆã¨PDFç‰ˆã‚ã‚Šã¾ã™ ç§ãŒè³¼å…¥ã—ãŸã¨ãã¯ãƒšãƒ¼ãƒ‘ãƒ¼ãƒ–ãƒƒã‚¯ç‰ˆã—ã‹ãªã‹ã£ãŸã®
yass 2012/10/23
sql

index

book
ãƒªãƒ³ã‚¯
Covering Index ã¨ self-join ã¨ MySQL - blog.nomadscafe.jp
æŸã‚µãƒ¼ãƒ“ã‚¹ã®ã‚¯ã‚¨ãƒªãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã®ãŠè©±ã€‚ ãƒ–ãƒã‚°ã¨ã‹æ—¥è¨˜ã¨ã‹ãã†ã„ã†ã‚µãƒ¼ãƒ“ã‚¹ç³»ã§æ¬¡ã®ã‚ˆã†ãªãƒ†ãƒ¼ãƒ–ãƒ«ãŒã‚ã£ãŸã¨ã—ã¾ã™ã€‚ CREATE TABLE entries ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, posted_by TINYINT UNSIGNED NOT NULL, --#PCã€mobileãªã©ã©ã“ã‹ã‚‰æŠ•ç¨¿ã•ã‚ŒãŸã‹ã®ãƒ•ãƒ©ã‚° title VARCHAR(512) NOT NULL, body TEXT NOT NULL, created_at DATETIME NOT NULL, updated_at TIMESTAMP NOT NULL, status TINYINT UNSIGNED NOT NULL, INDEX (user_id,created_at
yass 2011/08/14
mysql

index

sql
ãƒªãƒ³ã‚¯
1