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
      Drop 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.

      Drop Index

      The DROP statement in MySQL database is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, like indexes, it will remove them permanently along with their associated data.
      Therefore, we can drop any index from a database table using two different SQL DROP queries.
      It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

      The MySQL DROP INDEX Statement

      The DROP INDEX statement in MySQL is used to delete an index from a table.

      Syntax

      Following is the syntax to drop an index using DROP INDEX statement 
      DROP INDEX index_name ON table_name;

      Example

      In this example, we first create a new table CUSTOMERS and adding an index to one of its columns (AGE) 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),       
         PRIMARY KEY(ID),
         INDEX(AGE)
      );
      Now, create another index on CUSTOMERS table. We are using CREATE INDEX statement here 
      CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);
      DROP INDEX Query
      Then, use the following query to drop the index created above.
      DROP INDEX NAME_INDEX ON CUSTOMERS;

      Verification

      To verify if the index has been dropped, display the table definition using DESC query below 
      DESC CUSTOMERS;
      As we can see in the following table, the index on NAME column is dropped.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      MUL
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      The MySQL ALTER... DROP Statement

      The ALTER DROP statement can also be used to drop an index in a MySQL table. This is just an alternative to the DROP INDEX statement, so it only works with the index that exists on a table.

      Syntax

      Following is the syntax of the DROP INDEX IF EXISTS in SQL 
      ALTER TABLE table_name DROP INDEX index_name;

      Example

      Let us see another example to drop the index from the CUSTOMERS table using the ALTER... DROP command as shown below 
      ALTER TABLE CUSTOMERS DROP INDEX AGE;

      Verification

      To verify if the index on AGE column has been dropped, display the table definition using DESC query below 
      DESC CUSTOMERS;
      As we can see in the following table, the index on NAME column is dropped.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Dropping PRIMARY KEY or UNIQUE Constraint

      The DROP INDEX statement in MySQL does not usually drop indexes like PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP command.

      Syntax

      Following is the syntax 
      ALTER TABLE table_name DROP constraint_name;

      Example

      In this example, we are using the following query to drop the PRIMARY KEY constraint present on the ID column of CUSTOMERS table 
      ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

      Verification

      To verify whether the primary key constraint is dropped from the table, describe the 'temp' table using DESC command as follows 
      DESC CUSTOMERS;
      The PRIMARY KEY constraint is finally dropped! Look at the table below
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Dropping an Index Using a Client Program

      We have seen how to drop an index from a MySQL database using SQL queries. In addition to it, we can also use other client programs to perform the drop index operation in the MySQL database.

      Syntax

      Following are the syntaxes to drop an index from a MySQL database using various programming languages
      PHPNodeJSJavaPython
      The MySQL PHP connector mysqli provides a function named query() to execute the DROP INDEX query in the MySQL database.
      $sql = "DROP INDEX index_name ON tbl_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 = "DROP INDEX tid ON tutorials_table";
      if ($mysqli->query($sql)) {
          printf("Index droped successfully!.");
      }
      if ($mysqli->errno) {
          printf("Index could not be droped!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Index droped successfully!.