Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • What are Mysql Indexes

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 192
    Comment on it


    A database index is a structure that improves the speed of retrieval of data from our table. Index is similar to the index of a book. If you want to find a chapter you look in the index first without scanning the whole page. Users cannot see the indexes, they are just used by database engines to search records very fast. Indexes can be created on per column basis.

    Indexes are basically used to speed up searching operation on a database table. If you are using indexes, then insert and update statement will take more time whereas select statement will become fast.

     

    Ex:

    If you have a table with the columns: name, email, address and empID and want to create an index to speed up, then you would need to create an index for empID. When you create this index, MySQL will build a lookup index where queries having empID can be run quickly. However, the name, email and address queries would not be any faster.

     

    How to create Mysql Index:

    CREATE TABLE employee_records (
        name VARCHAR(50), email VARCHAR(100),
        empID INT, INDEX (empID)
    )

     

     We can create two types of indexes:

     1. Simple Index

     2. Uniques Index:  A unique index means that two  no two have same index value.

     Ex:

    CREATE UNIQUE INDEX employee_index
    ON employee_records ( employeeID);

     

    How to display Index Information: We can use show index command  to show all the indexes

    SHOW INDEX FROM employee_records

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: