In the previous segment, you understood the importance of indexing in SQL. Now, let’s watch the upcoming video to learn how indexing can be implemented in MySQL using the ‘where’ clause.
So, as you learnt in this video, MySQL has a specific DDL statement called CREATE INDEX, which is used for specifying the attribute on which you want to create an index. Generally, you would not have the permission to create such indices on a database. In such cases, you can ask the Database Administrator (DBA) to create the index for you.
In the next video, you will learn the syntaxes for creating, adding and dropping an index. You will also learn how to implement these commands to optimise your queries.
Let’s reiterate the commands used for creating, adding and dropping an index in MySQL Workbench.
The command for creating an index is as follows:
ALTER TABLE table_name ADD INDEX index_name(column_1, column_2, …); |
The command for dropping an index is as follows:
ALTER TABLE table_name ADD INDEX index_name(column_1, column_2, …); |
The command for dropping an index is as follows:
Answer the questions given below to test your understanding of indexing in SQL.
There are two types of indexing. Learn about what these are and how they differ in the next segment.