The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Use a B-tree for the broadest set of ordinary SQL lookups: equality, ranges, and ordered retrieval. Use a hash index only for equality searches when your database, storage engine, and table type support it. Use a full-text index when you need word- or language-aware search across text, rather than exact-value matching. The right choice depends on what the query means and what the specific database implements—not on a universal speed ranking.
Choose by the query you need to run
| Query need | Starting point | Why |
|---|---|---|
| Equality or range comparisons, including <, <=, >=, >, BETWEEN, and many IN-style lookups; retrieve rows in sorted order | B-tree | Supports equality and ordered comparisons, and can provide sorted output. It is the general-purpose default in PostgreSQL and widely used in MySQL and SQL Server rowstore indexes. PostgreSQL index types; MySQL index use; SQL Server indexes. |
| Equality lookup only | Hash, if the target database and table model support it | Hash indexes are designed for equality comparisons, not range access or sorted output. Availability and constraints differ by product and engine. PostgreSQL index types; MySQL CREATE INDEX; SQL Server indexes. |
| Search by words, phrases, or language-aware text semantics | The database’s full-text search facility | Full-text search tokenizes text and supports search behavior that ordinary scalar indexes do not provide. Its supported data types, languages, and setup vary by product. PostgreSQL text-search indexes; MySQL column indexes; SQL Server Full-Text Search. |
What each index is for
B-tree: general-purpose values, ranges, and ordering
A B-tree is the practical first choice when a query compares ordinary column values, filters over a range, or asks for ordered results. PostgreSQL documents B-tree support for equality and range operators and notes that it can return rows in sorted order. SQL Server describes its rowstore indexes as B+ trees; the naming differs, but the key point for query design is that these indexes support ordered access. MySQL also uses BTREE for common index forms.
As an Amazon Associate I earn from qualifying purchases.
That flexibility makes B-tree a sensible default, not a guarantee that every query will use an index. The optimizer weighs the query, available indexes, and data distribution when choosing a plan.
Free tools Windows power users keep installed
One-click scans. No signup required.
Hash: equality, with engine-specific boundaries
A hash index is for matching a value by equality. It does not provide the ordered traversal needed for a range predicate such as created_at >= ... or for sorted output. Nor should it be treated as a general-purpose substitute for B-tree. Whether you can create one depends on the database product and, in some cases, the storage engine or table type.
#1 Best Overall
Full-text: words and linguistic search
Full-text search is appropriate when the application searches the content of text fields by tokens, phrases, or language-aware rules. It is distinct from exact equality or a normal range comparison. It is also not automatically the right solution for every query described as “search”: an exact match, a prefix lookup, a substring search, and a linguistic word search are different requirements.
How the choices work in PostgreSQL, MySQL, and SQL Server
PostgreSQL
- B-tree: PostgreSQL’s default index method supports equality and range comparisons and can return results in order. See PostgreSQL 17 index types.
- Hash: PostgreSQL hash indexes support equality comparisons. They are not the choice for range filtering or ordered retrieval. See PostgreSQL 17 index types.
- Full-text: PostgreSQL supports GIN and GiST indexes for text-search data. Its documentation identifies GIN as the preferred text-search index type; GiST is an alternative with a different representation and trade-offs. A text-search index is optional, though recurring searches may benefit from one. See PostgreSQL 16 text-search indexes.
MySQL
In MySQL, check the storage engine before choosing an index method. The 26.7 manual documents InnoDB and MyISAM ordinary indexes as BTREE; MEMORY (also called HEAP) supports HASH and BTREE; and NDB supports HASH and BTREE with caveats. InnoDB’s ordinary indexes therefore are not a general place to substitute a hash index for a B-tree. See MySQL CREATE INDEX and How MySQL uses indexes.
MySQL FULLTEXT indexes are available for InnoDB and MyISAM on supported CHAR, VARCHAR, and TEXT columns. They use full-text-specific behavior rather than being a normal index declared as USING BTREE or USING HASH. Confirm the deployed engine and release’s rules in the MySQL column-index documentation.
Microsoft SQL Server
SQL Server rowstore indexes use a B+ tree structure. Its hash indexes use an in-memory hash table and are associated with memory-optimized tables, rather than being a universal alternative for ordinary rowstore tables. See SQL Server indexes.
SQL Server Full-Text Search is a separate, token-based facility. The Full-Text Engine builds an inverted index and supports linguistic searches; language support, configuration, and population behavior differ from regular indexes. The feature is product- and version-sensitive, so verify the documentation for the SQL Server or Azure SQL product you run. The SQL Server 2025 documentation, for example, notes breaking changes to Full-Text Search. See SQL Server Full-Text Search.
Identify what “search” means before adding an index
Index choice follows the predicate and the result the application expects. A query that asks whether a value equals a known value is not the same as one that finds words in a document. Before choosing, pin down whether users need exact matching, range or sorted access, or token- and language-aware retrieval. Then check that the engine supports that operation for the relevant column and table model.
- Exact value: Start by considering a B-tree; consider hash only if the query is equality-only and the engine’s constraints make it appropriate.
- Range or ordered results: Choose an index method that supports ordered access, commonly B-tree.
- Words, phrases, or linguistic matching: Use the product’s full-text facility and confirm its supported languages, column types, and configuration.
Validate the index against the real workload
An index being eligible for a query does not mean the optimizer will choose it or that it will make every workload faster. Indexes also have operational costs, and full-text facilities may require their own configuration and population. Review the target engine’s execution plan with representative data and queries, and account for index maintenance as well as read behavior. The official product documentation establishes capability rules; it does not establish a universal performance winner among these index families.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
