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
      Non-Clustered 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.

      Non-Clustered Index

      Indexes in MySQL are used to retrieve the data much faster from the database tables or views. Users cannot see the indexes on the application level, but they work behind to speed up searches and queries.
      There are two types of Indexes in MySQL
      • Clustered Index
      • Non-Clustered Index
      A clustered index in MySQL can sort the data in a table manually by ordering all the rows in the table based on the key columns used to create it. On the other hand, a non-clustered index stores data in one location and indexes containing pointers to this data in another location.

      MySQL Non-Clustered Indexes

      Non-Clustered indexes store data in one location and its indexes in another location. These indexes contain pointers to the actual data.
      However, MySQL does not provide ways to explicitly create clustered and non-clustered indexes. A PRIMARY KEY is treated as a clustered index. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is a clustered index. All the other indexes on a table are non-clustered indexes.

      Syntax

      Following is the basic syntax to create a non-clustered index on a MySQL table 
      CREATE INDEX index_name ON table_name(column_name(s));

      Example

      Let us see an example to create a non-clustered index on a table named 'Students'. This table contains details of students like their Roll Number, Name, Age, and Department. Here, we are trying to apply the non-clustered index on columns Roll Number and Department, using the following query
      Let us first create the table Students using CREATE TABLE statement shown below 
      CREATE TABLE CUSTOMERS(
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE  INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (20, 2),
         PRIMARY KEY(ID)
      );
      Using the following query, create a non-clustered index on the NAME column 
      CREATE INDEX nc_index ON CUSTOMERS(NAME);
      Note − As MySQL does not have specific provision for Non-Clustered Index, we are using the usual CREATE INDEX statement.

      Verification

      To verify whether the INDEX is created on the table CUSTOMERS or not, display the table definition using DESC command 
      DESC CUSTOMERS;
      As we can see below, there are two indexes created on the CUSTOMERS table. The PRIMARY KEY index is a clustered index and the multi-index is a non-clustered index
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      
      NAME
      varchar(20)
      NO
      MUL
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18, 2)
      YES
      
      NULL
      

      Creating a Non-Clustered Index Using NodeJS

      In addition to using SQL queries to create non-clustered indexes, we can also create them on a MySQL database using a client program.
      The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database.

      Syntax

      Following is the syntax to create a non-clustered index in MySQL database using NodeJS 
      sql = "CREATE INDEX index_name ON table_name(column_name(s))";
      con.query(sql);

      Example

      Following are the implementation of this operation using NodeJS 
      var mysql = require('mysql2');
      var con = mysql.createConnection({
          host: "localhost",
          user: "root",
          password: "Nr5a0204@123"
      });
      
        //Connecting to MySQL
        con.connect(function (err) {
        if (err) throw err;
        console.log("Connected!");
        console.log("--------------------------");
      
        //Creating a Database
        sql = "create database TUTORIALS"
        con.query(sql);
      
        //Select database
        sql = "USE TUTORIALS"
        con.query(sql);
      
        //Creating table
        sql = "CREATE TABLE STUDENTS(RNO INT NOT NULL,NAME VARCHAR(50),AGE INT,DEPT VARCHAR(50));"
        con.query(sql);
      
        //Creating Index
        sql = "CREATE INDEX nc_index ON STUDENTS(RNO, DEPT);"
        con.query(sql);
      
        //Describing the Table
        sql = "DESC STUDENTS;"
        con.query(sql, function(err, result){
          if (err) throw err
          console.log(result)
        });
      });

      Output

      The output produced is as follows 
      Connected!
      --------------------------
      [
        {Field: 'RNO',Type: 'int',Null: 'NO',Key: 'MUL',Default: null,Extra: ''},
        {Field: 'NAME',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''},
        {Field: 'AGE',Type: 'int',Null: 'YES',Key: '',Default: null,Extra: ''},
        {Field: 'DEPT',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''}
      ]