SQL indexes on user-defined functions
use-the-index-luke.com/sql/where-clause/functions/user-defined-functionsФункции должны быть чистыми, чтобы по ним работал индекс
Case-Insensitive Search in SQL
use-the-index-luke.com/sql/where-clause/functions/case-insensitive-searchUsing UPPER or LOWER for case-insensitive search is not a good practice. Prefer using a _CI collation if possible or at least add the right indexes to make your query fast.
The right column order in multi-column indexes
use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keysThe most important consideration when defining a concatenated index is how to choose the column order so it can be used as often as possible.
Например, поиск по имени без фамилии бесполезен. Значит, составной индекс должен начинаться с фамилии, а потом уже будет имя.
Slow Indexes in RDBMS
use-the-index-luke.com/sql/anatomy/slow-indexesAn index lookup requires three steps: (1) the tree traversal; (2) following the leaf node chain; (3) fetching the table data. The tree traversal is the only step that has an upper bound for the number of accessed blocks—the index depth. The other two steps might need to access many blocks—they cause a slow index lookup.
Modern SQL: A lot has changed since SQL-92
modern-sql.comSQL has evolved. Beyond the relational model. Discover it now.