Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 65 - Modular_V2.0_SQL_Flipbook
P. 65

    Standard Index in MySQL is created using the following syntax:

                         CREATE INDEX index_name ON table_name (column_name);
                     An index for table STUDENTS is created as follows:

                                                                   STUDENTS

                                       Stu_id            Stu_name               Class                City
                                          1                 Aisha                10A               Mumbai
                                          2                 Ravi                 10B                 Delhi
                                          3                 Sara                 10A                Kolkata
                                          4                  Ali                 10C               Chennai
                                          5                 Neha                 10A                 Delhi

                         CREATE INDEX idx_city ON STUDENTS(City);
                     This index will simplify and quicken the search process for finding any desired data in table
                     STUDENTS.
                     Let‘s discuss the different types of MySQL Indexes, each used for different purpose:

                     1.  Primary Index: This index is associated with the creation of primary key. It provides a unique
                         identity to each row and does not allow NULL values.
                         -- Primary Index

                         ALTER TABLE table_name
                         ADD PRIMARY KEY (column_name);
                     2.  Unique Index: This index is associated with the application of unique constraint to a column. A
                         table can have multiple unique indexes.
                         -- Unique Index

                         CREATE UNIQUE INDEX index_name
                         ON table_name(column_name);
                     3.  Full-Text Index: This type of index works with columns that have large quantities of text and
                         it helps in quick data-based searches.

                         -- Full-Text Index
                         CREATE FULLTEXT INDEX index_name
                         ON table_name(text_column_name);
                     4.  Composite Index: This type of index is created with the creation of a composite primary key is
                         defined, where more than one column is used to uniquely identify a row. It helps in resolving
                         queries that work on multiple columns.

                         -- Composite Index
                         CREATE INDEX index_name
                         ON table_name(column1, column2);
                     5.  Spatial Index: It works effectively on spatial data that is formed over geographical data types.

                         -- Spatial Index
                         CREATE SPATIAL INDEX index_name
                         ON table_name(spatial_column_name);

                                                                                                                         63
                                                                                            Advanced Features of MySQL

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