Creation of Indexes in MariaDB
Creating a Secondary Index on product_name
Query:
CREATE INDEX index_product_name ON sales (product_name);
Output:
Explanation:
- This index is useful for queries searching for sales related to a specific product.
- It spped up faster lookups based on the product_name column.
Creating a Composite Index on sale_date and quantity
Query:
CREATE INDEX index_sale_quantity ON sales (sale_date, quantity);
Output :
Explanation:
- This composite index is useful for range queries or sorting based on both sale_date and quantity.
- This index helps with searching or ordering info based on the sale date and quantity.
- It improves searches that use group of columns.
Creating a Unique Index on transaction_id
Query:
CREATE UNIQUE INDEX index_transaction ON sales (transaction_id);
Output:
Explanation:
- The transaction_id is the primary key. So, making a different unique index for it doesn’t make sense.
- But, if we don’t use the primary key for search purposes, a unique index could help keep the data correct.
Creating a Full-Text Index on product_name for Text Searches
Query:
CREATE FULLTEXT INDEX index_product ON sales (product_name);
Output:
Explanation:
- If we expact for performing full-text searches on product names.
- A full-text index can mostly improve search performance.
Creating an index in MariaDB
MariaDB is an open–source and database management system. MariaDB is used for several purposes like as data warehousing, e-commerce, and logging applications. MariaDB is faster than MySQL in the replication and querying process. MariaDB supports invisible columns and temporary table space.
In this article, We will understand Creating an Index, the need for Indexing and creating various indexes for understanding, and so on.
It is useful to understand the MariaDB CREATE INDEX command. This boosts the speed of queries and makes databases work better, it increases query speed and improves database function.