#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Help with understanding MySQL Code


    Hello, i'm new to MYSQL, and i was just wondering what the highlighted code shown below mean? I know that the code below is used to create tables in a MYSQL database, but what exactly does the red highlighted KEY word do since a primary key is already set? Also, why does the orange highlighted code do, and when should i use it?

    MySQL Code:
    CREATE TABLE `items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `subcat_id` INT(11) NOT NULL,
      `name` varchar(64) NOT NULL,
      PRIMARY KEY (`id`),
     <font color="Red"> KEY `name` (`name`)</font>
    );
     
    CREATE TABLE item_link (
      id INT(11) NOT NULL AUTO_INCREMENT,
      branch_id INT(11) NOT NULL,
      item_id INT(11) NOT NULL,
      PRIMARY KEY (`id`),
      <font color="DarkOrange">KEY composite (`branch_id`, `item_id`)</font>
    );


    Thanks!
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    The highlighted lines create indexes on certain columns to make lookups faster. For example, if your application runs the query:
    SELECT id, subcat_id FROM items WHERE name = 'abc';
    Then you would want to have an index on the name column.

    When you have an index on a column, MySQL is able to use more efficient algorithms to find specific values in it. If a column doesn't have an index and MySQL needs to find a specific value, then it has to perform a scan of the entire table, which is slow and inefficient.

    Indexes can also be used to a limited extent to enforce data constraints like uniqueness.

    An index can be made on multiple columns to speed up queries that perform a lookup of values in both columns at the same time, that is what the orange line is doing.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo