• Home
  • Courses
  • School
  • Programs
  • Problems
  • Contact Us
  • My account
  • Register

Have any question?

(+91) 98222 16647
info@simplycoding.in
RegisterLogin
Simply Coding
  • Home
  • Courses
  • School
  • Programs
  • Problems
  • Contact Us
  • My account
  • Register

Data Management

Index Commands in SQL

  • Categories Data Management, Database

Database has solved the problem of storing large amount of data for us. However the key requirement is also that when we retrieve information from a database it needs to be quick. One of the ways databases achieve this is via indexes. In this post we are going to learn about indexes in database.

An index is nothing but a special lookup table which stores value of index column in a sorted manner and then a pointer to actual location of the row in the original table. This makes any kind of data retrieval very fast.  They are useful in sorting data and give much better performance.

                                                                                 

Now let’s learn how to create indexes.

Indexes can be created while you are creating the table itself or can be created after the table is created.

To create it along with the table the syntax is as given, let’s use it in an example.

In the create table command, will have the keyword index followed by index name and then the column name on which index is to be created in brackets. You can also specify ascending or descending order.

To create it on an already existing table we use the CREATE INDEX command and then give the index name and then give ON which table we want the index followed by specific column names in bracket.

If you use the UNIQUE key word while creating the indexes, it does not allow multiple same values in the table.

To remove an index we use the DROP INDEX command with the index name and on which table it needs to be dropped.

Alternatively you can also use DROP index in ALTER Table command which also serves the same purpose.

To anytime view the indexes on a table we can use SHOW INDEXES command from the table. It will showcase all indexes that exists for the table.

                        

                         

Do note that Indexes are automatically created for primary key constraints and unique constraints.

While indexes are useful, they have some disadvantages too:  that performance of update, insert and delete decreases. This is because any update to table also needs update to index table too. These index tables also consume space and more the indexes and more the data in the table, more space they consume. So it is advised that indexes are always created when they are actually needed.

General guidelines is that index should be created when a column contains wide range of values, is typically not null and it is frequently used in search conditions.

And it should be avoided if the table is small and if columns are not typically used for search condition or column is updated too frequently.

Check out our video: https://youtu.be/0qv7oXlD0uk

  • Share:
author avatar
Simply Coding

Previous post

SQL Select Statement: Tutorial
December 14, 2020

Next post

Solve Pronic Number Program
December 22, 2020

You may also like

Screenshot_20201209-113227__01
SQL Select Statement: Tutorial
14 December, 2020
Screenshot_20201209-111403__01
SQL: Join
10 December, 2020
Screenshot_20201209-124632__01
SQL: Introduction
10 December, 2020

Leave A Reply Cancel reply

You must be logged in to post a comment.

Categories

  • Uncategorized
  • Programs
    • Python
    • Java
  • Problems
    • Python
    • Java
    • Web Development
      • Internet
    • Emerging Technologies
  • Notes
    • General
    • QBasic
    • MS Access
    • Web Development
      • XML
      • HTML
      • JavaScript
      • Internet
    • Database
    • Logo Programming
    • Scratch
    • Emerging Trends
      • Artificial Intelligence
      • Internet of Things
      • Cloud Computing
      • Machine Learning
    • Computer Fundamentals
      • Computer Networks
      • E-Services
      • Computer Hardware
    • Python
    • Java
  • School
    • ICSE
      • Computers Class 9
        • Java Introduction
        • Tokens & Data Types
        • Java Operators
        • Math Library
        • if & switch
        • For & While
        • Nested loops
      • Computer Class 10
        • Sample Papers
        • OOPS concepts
        • Functions in Java
        • Constructors
        • Arrays in Java
        • Strings in Java
    • SSC
      • IT Class 11
        • IT Basics
        • DBMS
        • Web Designing
        • Cyber Laws
      • IT Class 12
        • Web Designing
        • SEO
        • Advanced JavaScript
        • Emerging Tech
        • Server Side Scripting
        • E-Com & E-Gov
      • Computer Science 11
      • Computer Science 12
    • CBSE
      • Computer 9
        • Basics of IT
        • Cyber Safety
        • Scratch
        • Python
      • Computer 10
        • Sample Papers
        • Networking
        • HTML
        • Cyber Ethics
        • Scratch
        • Python
      • Computer Science 11
        • Computer Systems
        • Python 11
          • Python Basics
          • Python Tokens
          • Python Operators
          • Python if-else
          • Python loops
          • Python Strings
          • Python List
          • Python Tuple
          • Python Dictionary
          • Python Modules
        • Data Management
      • Computer Science 12
        • Sample Papers
        • Python 12
          • Python Functions
          • Python File Handling
          • Python Libraries
          • Python Recursion
          • Data Structures
        • Computer Networks
        • Data Management
    • ISC
      • Computer Science 11
        • Introduction to Java
        • Values & Data Types
        • Operators
        • if & switch
        • Iterative Statements
        • Functions
        • Arrays
        • String
        • Data Structures
        • Cyber Ethics
      • Computer Science 12
        • Sample Papers
        • Boolean Algebra
        • OOPS
        • Wrapper Classes
        • Functions
        • Arrays
        • String
Simply Coding Computer Courses for School                Privacy Policy     Terms of Use     Contact Us

© 2021 Simply Coding

Login with your site account

Lost your password?

Not a member yet? Register now

Register a new account

Are you a member? Login now