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 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:
searching based on sale_date and quantity
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:
Full-Text Search on product_name
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:
Query without Indexing
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.
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.