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
      Vertical Partitioning

      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.

      Vertical Partitioning

      The MySQL Partitioning is used to divide large tables into smaller partitions that are stored in different physical locations and are treated as separate tables. Even though the smaller partitions are managed individually, they are still part of the main table.
      There are two forms of partitioning in MySQL: Horizontal Partitioning and Vertical Partitioning.

      The MySQL Vertical Partitioning

      The MySQL Vertical partitioning divides the table into multiple tables based on columns, rather than rows.
      There are two main types of vertical partitioning in MySQL, each serving specific purposes
      • RANGE Columns Partitioning
      • LIST Columns Partitioning
      Both Range Columns Partitioning and List Columns Partitioning support various data types, including integer types (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT), string types (CHAR, VARCHAR, BINARY, and VARBINARY), as well as DATE and DATETIME data types.

      Range Columns Partitioning

      The MySQL Range Columns partitioning uses one or more columns as partition keys to divide the data into partitions based on a defined range of column values.
      The values in these columns are compared to predefined ranges, and each row is assigned to the partition that encompasses the range containing its column values.

      Example

      In the following query, we are creating a table named INVENTORY and dividing it into three partitions based on "product_quantity" and "product_price" columns. Rows with specific values in these columns are stored in their corresponding partitions 
      CREATE TABLE INVENTORY (
         id INT,
         product_name VARCHAR(50),
         product_quantity INT,
         product_price int
      )
         PARTITION BY RANGE COLUMNS(product_quantity, product_price) (
         PARTITION P_low_stock VALUES LESS THAN (10, 100),
         PARTITION P_medium_stock VALUES LESS THAN (50, 500),
         PARTITION P_high_stock VALUES LESS THAN (200, 1200)
      );
      Here, we are inserting rows into the above-created table 
      INSERT INTO INVENTORY VALUES
      (1, 'Headphones', 5, 50),
      (2, 'Mouse', 15, 200),
      (3, 'Monitor', 30, 300),
      (4, 'Keyboard', 60, 600),
      (5, 'CPU', 100, 1000);
      Following is the INVENTORY table obtained
      id
      product_name
      product_quantity
      product_price
      1
      Headphones
      5
      50
      2
      Mouse
      15
      200
      3
      Monitor
      30
      300
      4
      Keyboard
      60
      600
      5
      CPU
      100
      1000
      Now that we have some data in the INVENTORY table, we can display the partition status to see how the data is distributed among the partitions using the following query 
      SELECT PARTITION_NAME, TABLE_ROWS
      FROM INFORMATION_SCHEMA.PARTITIONS 
      WHERE TABLE_NAME='inventory';
      You will see in the output below that the respective columns are assigned to their respective partitions based on the defined range values
      PARTITION_NAME
      TABLE_ROWS
      P_high_stock
      2
      P_low_stock
      1
      P_medium_stock
      2
      Displaying Partitions
      We can also display data from specific partitions using the PARTITION clause. For instance, to retrieve data from partition P_high_stock, we use the following query 
      SELECT * FROM inventory PARTITION (P_high_stock);
      It will display all the records in partition P_high_stock
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      4
      Keyboard
      60
      600
      5
      CPU
      100
      1000
      Similarly, we can display other partitions using the same syntax.

      List Columns Partitioning

      The MySQL List columns partitioning uses one or more columns as partition keys and assigns records to partitions based on specific values in those columns. This method is handy when you want to group data into partitions based on discrete values or categories.

      Example

      Let us create a table named "EMPLOYEES" and partition it using LIST COLUMNS partitioning based on the "department" column 
      CREATE TABLE EMPLOYEES (
         id INT,
         first_name VARCHAR(50),
         last_name VARCHAR(50),
         hiring_date DATE,
         department VARCHAR(50)
      )
         PARTITION BY LIST COLUMNS(department) (
         PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
         PARTITION p_engineering VALUES IN ('Engineering', 'Research'),
         PARTITION p_operations VALUES IN ('Operations')
      );
      Here, we are inserting records into above-created table 
      INSERT INTO EMPLOYEES VALUES 
      (1, 'John', 'Doe', '2020-01-01', 'Sales'),
      (2, 'Jane', 'Doe', '2020-02-01', 'Marketing'),
      (3, 'Bob', 'Smith', '2020-03-01', 'Engineering'),
      (4, 'Alice', 'Johnson', '2020-04-01', 'Research'),
      (5, 'Mike', 'Brown', '2020-05-01', 'Operations');
      Following is the EMPLOYEES table obtained
      id
      first_name
      last_name
      hiring_date
      department
      1
      John
      Doe
      2020-01-01
      Sales
      2
      Jane
      Doe
      2020-02-01
      Marketing
      3
      Bob
      Smith
      2020-03-01
      Engineering
      4
      Alice
      Johnson
      2020-04-01
      Research
      5
      Mike
      Brown
      2020-05-01
      Operations
      We can display the partition status of the EMPLOYEES table to see how the data is distributed among partitions using the following query 
      SELECT PARTITION_NAME, TABLE_ROWS 
      FROM INFORMATION_SCHEMA.PARTITIONS 
      WHERE TABLE_NAME='EMPLOYEES';
      It will display the partitions and the number of rows in each partition based on the department values
      PARTITION_NAME
      TABLE_ROWS
      p_engineering
      2
      p_operations
      1
      p_sales
      2
      P