Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Create Index

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       Create Index

      A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
      Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table.
      We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table.

      Creating Indexes on New Table

      If we want to define an index on a new table, we use the CREATE TABLE statement.

      Syntax

      Following is the syntax to create an index on a new table 
      CREATE TABLE(
       column1 datatype PRIMARY KEY,
       column2 datatype,
       column3 datatype,
       ...
       INDEX(column_name)
      );

      Example

      In this example, we are create a new table CUSTOMERS and adding an index to one of its columns using the following CREATE TABLE query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),       
         INDEX(ID)
      );
      To verify whether the index has been defined or not, we check the table definition using the following DESC statement.
      DESC CUSTOMERS;

      Output

      The table structure displayed will contain a MUL index on the ID column as shown
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      MUL
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Creating Indexes on Existing Table

      To create an index on existing table, we use the following SQL statements
      • With CREATE INDEX Statement
      • With ALTER Command

      CREATE INDEX Statement

      The basic syntax of the CREATE INDEX statement is as follows 
      CREATE INDEX index_name ON table_name;
      In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here 
      CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);
      To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below 
      DESC CUSTOMERS;

      Output

      As we can see in the table below, a composite index is created on the 'NAME' column of CUSTOMERS table.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      MUL
      NULL
      
      NAME
      varchar(20)
      NO
      MUL
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      ALTER... ADD Command

      Following is the basic syntax of ALTER statement 
      ALTER TABLE tbl_name ADD INDEX index_name (column_list);
      Let us use ALTER TABLE... ADD INDEX statement in the following example to add an index to the CUSTOMERS table 
      ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

      Output

      As we can see in the table below, another composite index is created on the 'AGE' column of CUSTOMERS table.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      MUL
      NULL
      
      NAME
      varchar(20)
      NO
      MUL
      NULL
      
      AGE
      int
      NO
      MUL
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Simple and Unique Index

      A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index 
      CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

      Example

      Following example creates a unique index on the table temp 
      CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

      Composite Indexes

      We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows 
      CREATE INDEX index_name
      on table_name (column1, column2);

      Example

      Following query creates a composite index on the ID and Name columns of the above created table 
      CREATE INDEX composite_index on CUSTOMERS (ID, Name);

      Creating an Index Using Client Program

      In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program.

      Syntax

      Following are the syntaxes to create an index in a MySQL database using various programming languages
      PHPNodeJSJavaPython
      The MySQL PHP connector mysqli provides a function named query() to execute the CREATE INDEX query in the MySQL database.
      $sql=" CREATE INDEX index_name
         ON table_name (column_name)";
      $mysqli->query($sql);

      Example

      Following are the implementations of this operation in various programming languages
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // CREATE INDEX
      $sql = "CREATE INDEX tid
              ON tutorials_table (tutorial_id)";
      if ($mysqli->query($sql)) {
          printf("Index created successfully!.");
      }
      if ($mysqli->errno) {
          printf("Index could not be created!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Index created successfully!.