When to Use ISAM
ISAM is a versatile storage structure because it supports both exact match and range retrievals. ISAM indexes and main pages are static—if you are appending many rows, remodify to avoid overflow pages. For tables that are mostly static, ISAM can be preferable to B‑tree.
Because ISAM indexes are static, no locking needs to be done on the ISAM index. In a heavily concurrent update environment, this feature makes ISAM more appealing than B-tree, where pages of the index must be locked when splitting or updating occurs.
ISAM is a good storage structure to use when the table is relatively static, and retrievals tend to use any of the following:
• Pattern matching
• Ranges of key values
• Only the leftmost part of a multi-column key
ISAM is a poor storage structure to use in any of these cases, which causes overflow pages:
• The table is growing at a rapid rate.
• The table is too large to modify.
• The key is sequential, that is, each key number is higher than the last and the data is not static. This is because adding data with sequential keys adds a lot of overflow pages at the last main page.
Last modified date: 04/03/2024