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.
MariaDB Create Index
In MariaDB, the CREATE INDEX statement is useful for making indexes on table columns. Think of an index as a tool for making table data search easy and fast. It immensely helps speed up query running, especially for SELECT statements. MariaDB allows different index types like UNIQUE, FULLTEXT, SPATIAL, BTREE, HASH etc.
Making indexes in MariaDB is key to speeding up data searches. The CREATE INDEX command lets database managers organize data better. By making an index, we can make our MariaDB system respond faster.
Need for Indexing
Indexes help speed up data searches. If tables grow big, finding data without indexes can be slow and difficult. This can make the system to lag.
Indexes make data searches faster and the system more responsive. This is very useful when working with large data and complex searches.
MariaDB Create Index Statement Example
Let’s look at a real-life example. We’ll explore the MariaDB CREATE INDEX command. We’ll study different approaches and what they mean.
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:
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:
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.
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:
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:
Explanation:
- We can also add indexes 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:
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 faster.
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:
Explanation:
- The search improves with the composite index index_sale_quantity.
- It makes searches quicker using both sale_date and quantity.
- The EXPLAIN command shows this index helps focus the results.
3. Query Utilizing Full-Text Search on product_name
Query:
SELECT * FROM sales WHERE MATCH(product_name) AGAINST('Mobile');
Output:
Explanation:
- Full-text indexes, 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 they work. They make searching for specific words in text way easier!
4. Query without Indexing
Query:
EXPLAIN SELECT * FROM sales WHERE total_amount > 3000.00;
Output:
Explanation:
- This query doesn’t single out the total_amount column.
- The EXPLAIN output may suggest looking at every item in the table, which could slow things down if there’s tons of data.
Primary Vs Secondary Index
Criteria |
Primary Index |
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 index is key to searches records easily. Secondary indexes speed up searches on certain columns. So sometimes, we use them to help. Other times, we don’t. It helps keep things quick and efficient.
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.
Contact Us