Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 66 - Modular_V2.0_SQL_Flipbook
P. 66

    Dropping an Index

                  To delete an existing index from a table, you can use DROP INDEX command, as shown below:

                       DROP INDEX index_name ON table_name;
                  In case you want to remove a primary key index, use the following syntax:

                       ALTER TABLE table_name DROP PRIMARY KEY;

                  Showing all existing Indexes

                  To view all the indexes applied on a given table, use the SHOW INDEX command as follows:

                       SHOW INDEX FROM table_name;
                  Points to remember while using indexes:

                  1.   An SQL programmer must identify the high-volume queries that consume significant time and
                      resources to determine if a Full-Text Index is needed.

                  2.   The columns chosen for indexing must be appropriately placed against the clauses (e.g., WHERE,
                      ORDER BY, JOIN) when creating the indexes.

                  3.   Over-indexing does not solve any purpose, so be mindful of the number of indexes created.
                  4.   If multiple columns need to be indexed, a composite index should be used.

                  Proper utilisation of SQL indexes can significantly improve the performance of your database.

                      Recap

                            A subquery can be defined as a query within another query.
                             A JOIN clause is used to combine  rows from two or more tables based on a related column

                          between them.

                            A view is a virtual table created based on one or more tables.
                            An index is a structure that is used to improve the performance of database activity.

                                                           Exercise

                  A.   Tick ( ) the correct option.
                     1.  Which of the following can be defined as a query within another query?

                        a.  Index                                        b.  Subquery
                        c.  Join                                         d.  View

                     2.  Which of the following is the correct syntax for CREATE INDEX command?
                        a.  INSERT INDEX index_name ON table_name;

                        b.  INSERT INDEX index_name ON database_name;

                    64
                          Touchpad MODULAR (Ver. 2.0)

| 61 62 63 64 65 66 67 68 69 70 71 |