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

      Clustered Index

      Indexes in MySQL are used to retrieve the data much faster from the database. We (users) cannot see the indexes, but they work behind to speed up searches and queries. They are categorized into two types: clustered and non-clustered indexes.
      A clustered index can sort the data in a table manually. When data is inserted into the column with clustered index, the records are automatically sorted in a specified order. So, each table can only have one clustered index since it determines the sort order of the data.

      MySQL Clustered Indexes

      MySQL database does not have separate provisions for Clustered indexes. They are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index.
      If a table has no Primary Key or UNIQUE index, MySQL will internally create a hidden clustered index named GEN_CLUST_INDEX on a column that contains the row ID values.
      The rows of a table are ordered using row ID values generated by InnoDB.

      Example

      Let us create a table named CUSTOMERS using the following query 
      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)
      );
      Now, we will insert some values in to the above created table using the INSERT statement 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', '32', 'Ahmedabad', 2000),
      (2, 'Khilan', '25', 'Delhi', 1500),
      (3, 'Kaushik', '23', 'Kota', 2500),
      (4, 'Chaitali', '26', 'Mumbai', 6500),
      (5, 'Hardik','27', 'Bhopal', 8500),
      (6, 'Komal', '22', 'MP', 9000),
      (7, 'Muffy', '24', 'Indore', 5500);
      The table will be created as follows
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      Using the following query, we can list all the indexes created on the CUSTOMERS table 
      SHOW INDEX FROM CUSTOMERS\G

      Output

      As we can see in the output below, the PRIMARY KEY is created on the ID column of CUSTOMERS table.
      *************************** 1. row ***************************
              Table: customers
         Non_unique: 0
           Key_name: PRIMARY
       Seq_in_index: 1
        Column_name: ID
          Collation: A
        Cardinality: 7
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      1 row in set (0.01 sec)