MariaDB is an open–source and database managemeÂnt 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 queÂries and makes databases work beÂtter, it increaseÂs query speed and improveÂs database function.
MariaDB Create Index
In MariaDB, the CREATE INDEX stateÂment is useful for making indexeÂs on table columns. Think of an index as a tool for making table data seÂarch easy and fast. It immensely heÂlps speed up query running, eÂspecially for SELECT statements. MariaDB allows diffeÂrent index types like UNIQUE, FULLTEXT, SPATIAL, BTREE, HASH etc.
Making indexeÂs in MariaDB is key to speeding up data seÂarches. The CREATE INDEX command lets database managers organize data betteÂr. By making an indeÂx, we can make our MariaDB system respond fasteÂr.
Need for Indexing
IndexeÂs help speed up data seÂarches. If tables grow big, finding data without indexeÂs can be slow and difficult. This can make the systeÂm to lag.
Indexes make data seÂarches faster and the systeÂm more responsive. This is veÂry useful when working with large data and compleÂx searches.
MariaDB Create Index Statement Example
LeÂt’s look at a real-life exampleÂ. We’ll explore the MariaDB CREATE INDEX command. We’ll study different approaches and what they meÂan.
Lets create a table named sales that stores information about sales transactions. The table structure is look like this:
Query:
CREATE TABLE sales
(
transaction_id INT PRIMARY KEY,
product_name VARCHAR(50),
sale_date DATE,
quantity INT,
total_amount DECIMAL(10, 2)
);
Output:
sales TABLE
Now, inserting some data into the table
Query:
INSERT INTO sales
(transaction_id, product_name, sale_date, quantity, total_amount)
VALUES
(1, 'Laptop', '2024-01-01', 100, 5000.00),
(2, 'Mobile', '2024-01-02', 50, 2500.00),
(3, 'Laptop', '2024-01-03', 75, 3750.00),
(4, 'Radio', '2024-01-04', 120, 6000.00),
(5, 'Mobile', '2024-01-05', 60, 3000.00);
Output:
some sales data
Creation of Indexes in MariaDB
Creating a Secondary Index on product_name
Query:
CREATE INDEX index_product_name ON sales (product_name);
Output:
index on product
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 :
creating composite index
Explanation:
- This composite index is useful for range queries or sorting based on both sale_date and quantity.
- This index heÂlps with searching or ordering info based on the sale date and quantity.
- It improves seÂarches that use group of columns.
Creating a Unique Index on transaction_id
Query:
CREATE UNIQUE INDEX index_transaction ON sales (transaction_id);
Output:
creating a unique index
Explanation:
- The transaction_id is the primary key. So, making a different unique index for it doesn’t make seÂnse.
- But, if we don’t use the primary keÂy for search purposes, a unique indeÂx 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:
creating a fulltext index
Explanation:
- If we expact for performing full-text searches on product names.
- A full-text index can mostly improve search performance.
Dropping an Index
Query:
Let’s drop an index called index_sale_quantity that we have created above.
DROP INDEX index_sale_quantity ON sales;
Output:
removing index
Explanation:
- If the composite index is no longer required or needs any changes.
- It can be dropped using the DROP INDEX statement.
Creating Index Using ALTER TABLE
Query:
ALTER TABLE sales ADD INDEX index_total (total_amount);
Output:
add index by altering table
Explanation:
- We can also add indeÂxes using ALTER TABLE statement.
- In this example we add an index on a column named total_amount.
After Making the Indexes Some Search Examples
Lets take a example to clear the MariaDB CREATE INDEX statement.
Now, let’s perform various index queries and analyze the results.
1. Query Using the Secondary Index on product_name
Query:
EXPLAIN SELECT * FROM sales WHERE product_name = 'Laptop';
Output:
search on product name
Explanation:
- The query uses the secondary index named index_product_name in the product name column.
- The result from the EXPLAIN statement tells us how the query is executed.
- It shows that the list is used to find things fasteÂr.
2. Query using the Composite Index on sale_date and quantity_sold
Query:
EXPLAIN SELECT * FROM sales WHERE sale_date = '2024-01-03' AND quantity > 70;
Output:
searching based on sale_date and quantity
Explanation:
- The seÂarch improves with the composite index index_sale_quantity.
- It makes searcheÂs quicker using both sale_date and quantity.
- The EXPLAIN command shows this index heÂlps focus the results.
3. Query Utilizing Full-Text Search on product_name
Query:
SELECT * FROM sales WHERE MATCH(product_name) AGAINST('Mobile');
Output:
Full-Text Search on product_name
Explanation:
- Full-text indeÂxes, like index_product, help find specific words in the product_name column.
- These aren’t like normal indexes. They’re made for searching text.
- The EXPLAIN command might not always clear up how theÂy work. They make searching for specific words in teÂxt way easier!
4. Query without Indexing
Query:
EXPLAIN SELECT * FROM sales WHERE total_amount > 3000.00;
Output:
Query without Indexing
Explanation:
- This query doeÂsn’t single out the total_amount column.
- The EXPLAIN output may suggest looking at every iteÂm in the table, which could slow things down if thereÂ’s tons of data.
Primary Vs Secondary Index
Creation Trigger
|
Automatically with PRIMARY KEY
|
Explicitly with CREATE INDEX
|
Uniqueness
|
Enforces uniqueness
|
Does not enforce uniqueness
|
Number of Indexes
|
Only one allowed per table
|
Multiple allowed in the table
|
Column Selection
|
Typically on primary key column(s)
|
Can be on any column(s)
|
Purpose
|
Uniquely identifies records
|
Improves query performance
|
Storage Overhead
|
May have additional storage overhead
|
Can contribute to storage overhead
|
Deletion Impact
|
Removes primary key constraint
|
Deletion does not affect table structure
|
Query Optimization
|
valuable for primary key lookups
|
Enhances performance for specific queries
|
The primary indeÂx is key to searches records easily. SeÂcondary indexes speeÂd up searches on certain columns. So someÂtimes, we use theÂm to help. Other times, we don’t. It helps keep things quick and eÂfficient.
Conclusion
In this article, We have learned about how to set up indexes in MariaDB is crucial for boosting database speed. The CREATE INDEX command speeds up the lookup of data. Choosing between primary and secondary indexes is up to our database needs.
By setting up indexes, we make our MariaDB database work better overall. Index types in MariaDB serve different query needs. Learning about the CREATE INDEX command is important. It helps us form a database that meets real world needs. Using too many or wrong indexes can cause storage issues and slow things down.
Share your thoughts in the comments
Please Login to comment...