[Á¦¸ñ] MySQL INDEX¸¦ Ãß°¡ÇÏ´Â ¹æ¹ýÁ» °¡¸£ÃÄ ÁÖ¼¼¿ä. - ÀÛ¼ºÀÚ : »êÀÌ san2(at)linuxchannel.net - ÀÛ¼ºÀÏ : 2002.11.18 http://www.phpschool.com/bbs2/inc_view.html?id=31160&code=database2 ¿¡¼­ ´äº¯ÇÑ ³»¿ëÀ» ´Ù½Ã °¡Á®¿Ô½À´Ï´Ù. *Ãß°¡ÇÑ ³»¿ë) WHERE Àý¿¡¼­ '=' ¿¬»ê ¿Ü¿¡ ORDER BY, GROUP BY ´ÙÀ½¿¡ ÀÚÁÖ »ç¿ëÇÏ´Â Ä÷³µµ ºÐ¼®´ë»ó¿¡ ³Ö¾î¾ß ÇÕ´Ï´Ù. -- Áú¹® ³»¿ë --------------------------------------------------------- >ÇöÀç µðºñ°¡ ±²ÀåÈ÷ ´À·ÁÁ³½À´Ï´Ù >µðºñ ±¸Á¶´Â ÀÌ·¸±¸¿° >À妽º¸¦ ¾îÄÉ Àâ¾Æ¾ß ÇÒ±î¿ä? >ÇöÀç´Â primary ¸¸ Çߴµ¥ >À妽º¸¦ Ãß°¡ÇÏ·Á¸é ¾î¶»°Ô ÇؾßÇÏ´ÂÁö >ÀÚ¼¼È÷Á» °¥ÄÑÁÖ¼¼¿ä.. >¿À´Ã Åä¿äÀÏÀε¥ Áý¿¡ ¸ø°¥°Í°°¾Æ¿ë~~ >ºÎŹµå¸³´Ï´Ù > >+------------------------+-------------+------+-----+---------+-------+ >| Field | Type | Null | Key | Default | Extra | >+------------------------+-------------+------+-----+---------+-------+ >| serial_no | int(10) | | PRI | 0 | | >| random_id | varchar(10) | | | | | >| random_password | varchar(10) | | | | | >| ip_check | varchar(15) | | | | | >| sendmail_time | varchar(19) | YES | | NULL | | >| start_time_check | varchar(19) | YES | | NULL | | >| end_time_check | varchar(19) | YES | | NULL | | >| confirm_time | varchar(6) | | | | | >| receive_customer | varchar(20) | YES | | NULL | | >| receive_customer_email | varchar(30) | | | | | >| send_member | varchar(10) | | | | | >| send_member_email | varchar(30) | | | | | >| url | varchar(20) | | | | | >| name | varchar(30) | | | | | >| title | varchar(50) | | | | | >| contents | varchar(50) | YES | | NULL | | >| ibn_id | varchar(7) | YES | | NULL | | >| mail_content | text | YES | | NULL | | >| type | varchar(16) | YES | | NULL | | >| reffering | text | YES | | NULL | | >| auto_img | varchar(30) | YES | | NULL | | >| auto_flash | varchar(30) | YES | | NULL | | >| m_server | char(1) | YES | | NULL | | >+------------------------+-------------+------+-----+---------+-------+ -- ÀÌÇÏ ´äº¯ ³»¿ë ------------------------------------------------------ INDEX¸¦ Ãß°¡ÇÏ°íµµ ¼Óµµ°¡ ´À¸®´Ù¸é DB ±¸Á¶°¡ À߸øµÈ°ÍÀÔ´Ï´Ù. [DB±¸Á¶ º¯°æ¾øÀÌ INDEX Ãß°¡ÇÏ´Â ¹æ¹ý] ÇöÀç DB±¸Á¶¸¦ ¹Ù²Ù±â Èûµå¹Ç·Î ÇÁ·Î±×·¡¹ÖÇÑ PHP ÆäÀÌÁö³ª DB ·Î±×ÆÄÀÏÀ» ¿­¾îº¸¾Æ WHERE Àý¿¡¼­ °¡Àå ¸¹ÀÌ »ç¿ëÇÏ´Â Ä÷³À̸§À» ã¾Æ³»¾ß ÇÕ´Ï´Ù. Áï, ¾ÆÁÖ °£´ÜÇÏ°Ô Ä¿¸Çµå¶óÀο¡¼­, $ grep -i 'WHERE' *.php ¶Ç´Â(»ç¿ëºóµµ¼ö Âü°íÀÚ·á) # grep -i 'WHERE' /any/path/to/mysql_log_file ¾Æ¹«·¡µµ Á÷Á¢ ÆÄÀÏÀ» ¿­¾î¼­ È®ÀÎÇϴ°ÍÀÌ ´õ Á¤È®ÇÕ´Ï´Ù. ÀÌ·¸°Ô ã¾Æº¸¾Æ WHERE Àý¿¡¼­ »ç¿ëÇÏ´Â Ä÷³ÀÌ ¸¸¾à serial_no random_id confirm_time ibn_id ... À̶ó¸é WHERE Àý¿¡¼­ °¡Àå ¸¹ÀÌ »ç¿ëÇÏ´Â Ä÷³À» 5°³ ¹Ì¸¸Á¤µµ·Î °áÁ¤ÇÕ´Ï´Ù.(¶Ç´Â ±× ÀÌ»ó) ¸¸¾à °áÁ¤ÇÑ Ä÷³ÀÌ WHERE Àý¿¡¼­ '=' ¿¬»êÀÌ ¾Æ´Ñ >, <, !=, ISNULL µîÀÇ ¿¬»êÀÚ¸¦ »ç¿ëÇÏ¸é ¿ì¼±¼øÀ§·Î µÚ·Î ÇÕ´Ï´Ù. Áï ±× ÀÌÀ¯´Â, INDEX¿¡¼­´Â '=' ¿¬»ê¸¸ ±× È¿°ú°¡ Àֱ⠶§¹®ÀÔ´Ï´Ù. ¶ÇÇÑ WHEREÀý¿¡¼­ '=' ¿¬»êÀÌ µÎ°³ »ç¿ë(µÎ°³ÀÇ Ä÷³)Çϸé ÀÌ°ÍÀ» ÇϳªÀÇ INDEX·Î ¸¸µé¼ö ÀÖ½À´Ï´Ù. (¿¹: idpw(id, passwd)) ÀÌ¿Í °°ÀÌ Çѹø´õ ºÐ¼®ÇÏ¿© ÃÖÁ¾ÀûÀ¸·Î INDEX·Î »ç¿ëÇÒ Ä÷³À» ´Ù½Ã ±× ¼ø¼­¸¦ Á¤ÇÕ´Ï´Ù. ¸¸¾à Á¤ÇÑ ¼ø¼­°¡ serial_no ibn_id random_id confirm_time À̶ó¸é, mysql Å͹̳ο¡¼­(table À̸§ÀÌ mytable À̶ó¸é) »ç¿ë¹ý ALTER TABLE table_name ADD INDEX key_name(col1[,col2...]); mysql> ALTER TABLE mytable ADD INDEX serial_no(serial_no); mysql> ALTER TABLE mytable ADD INDEX ibn_id(ibn_id); mysql> ALTER TABLE mytable ADD INDEX random_id(random_id); mysql> ALTER TABLE mytable ADD INDEX confirm_time(confirm_time); ÀÌ¿Í °°ÀÌ ¸í·É¾î¸¦ ³»·Á INDEX¸¦ Ãß°¡ÇÏ¸é µË´Ï´Ù. Ãß°¡ÇÑ INDEX¸¦ È®ÀÎ ÇÏ°í ½Í´Ù¸é mysql> SHOW INDEX FROM mytable; ÇÏ¸é µË´Ï´Ù.