Creating an index on the name column of the people table
CREATE INDEX idx_nameON people (name);
Database indexing is a data structure based on one or more columns, usually a single column, to improve query performance. This data structure organises the values in the column in a way that enables faster search speed (O(log n)) compared to full table scan’s O(n)
One common data structure used for this purpose is the B-tree. The performance gain remains even if the values in the column are unique, as each unique value is represented as a node in the B-tree
Important
If the data you’re querying is stored in the index itself, the speed is even faster, as there’s no need to access the table to fetch the data from the table’s rows.
Attention
Indexes lose their effectiveness with LIKE %ZA%, as it is a pattern rather than a specific value that can be used to quickly locate data in O(log n) time. A full table scan is required to find all the rows that match this pattern.
However, Trigram search solves the issue with LIKE %ZA% by breaking down the string into trigrams (three-character substrings).
Performance analysis
Prefix the SQL query with EXPLAIN ANALYZE to check the time taken by the query, and use IGNORE INDEX() to force the database to avoid using an index.
