Summary  
The chapter covers hash indexing, explaining how a hash function maps keys to buckets for constant-time exact-match lookups and contrasting this with B-tree indexes by highlighting limitations such as no support for range queries or ordering.  

General domain of usage  
database query optimization

**Video: Introduction to Hash Indexes and Comparison with B-tree Indexes**

This video gives you a clear overview of how **hash indexes** work in SQL databases. You will see how hash indexes differ from **B-tree indexes** in structure and use cases, and learn why hash indexes are especially efficient for certain types of queries.

Hash indexes are a **specialized type of index** designed to speed up **equality searches** in SQL databases. Unlike **B-tree indexes**, which organize data in a balanced tree structure, hash indexes use a **hash function** to map key values directly to a specific location, or "bucket," in the index. 

When you search for a specific value, such as a product name in the `products` table, the hash function quickly locates the exact bucket where the value should be found. This makes hash indexes **extremely fast** for queries that look for exact matches, such as `WHERE name = 'Apple iPhone 14'`.

However, hash indexes are **not suitable for range queries or sorting**, because the hash function does not preserve any order among the indexed values.

-- Drop the hash index if it already exists to avoid errors
DROP INDEX IF EXISTS idx_products_name_hash;

-- Create a hash index on the name column of the products table
CREATE INDEX idx_products_name_hash
ON products USING HASH (name);

-- Test the effect of the index by searching for a specific product by name
SELECT * FROM products WHERE name = 'Apple iPhone 14';

Hash indexes can offer significant performance advantages in scenarios where queries frequently test for **equality** on a column, especially when the dataset is large and the column values are unique or nearly unique. For instance, if your application often needs to find products by their exact name, a hash index on the `name` column will outperform a B-tree index for those **equality searches**. However, if you need to perform **range queries**, such as finding all products with names between "A" and "M", or if you need to sort the results, a B-tree index will be more effective. Hash indexes are best used when you know your workload involves a lot of **point lookups** rather than ordered scans or range searches.

What type of queries are hash indexes best suited for?

Can hash indexes be used for range queries?

What is a limitation of hash indexes compared to B-tree indexes?

Learn about the important ACID properties that help keep data accurate in database transactions. Discover advanced ways to optimize your queries to improve database performance. 

Master advanced SQL concepts including ACID properties, query optimization, indexing, triggers, window functions, and data control. This course is designed for learners with a solid SQL foundation who want to deepen their understanding and practical skills in optimizing queries and leveraging advanced database features.

Hash Indexing

1. What type of queries are hash indexes best suited for?

2. Can hash indexes be used for range queries?

3. What is a limitation of hash indexes compared to B-tree indexes?