Context
Database overload You've got no indexes on some tables and every index in the world on other tables.Antipatterns
No index or not enough indexes OR too many indexes. Running queries that no index can help An index is like the sort in a telephone book. Is easy to find people by last name, because they are listed toghether.Solution
Indexes find values more quickly than searching the whole table. Every time we use INSERT, UPDATE, DELETE, the database has to recreate the index. An index can also help an UPDATE or DELETE by finding rows quickly. There is no SQL standard for indexes. Every brand is free to implement indexes differently. There's no benefit to creating indexes that you don't use.
CREATE TABLE bugs (
bug_id SERIAL PRIMARY KEY,
summary VARCHAR(80) NOT NULL,
...
INDEX (bug_id),
INDEX (summary),
...
);
Most databses create an index automatically for a primary key, index for bug_id is redundant.
An index for a long string datatype like VARCHAR(80) is larger than an index for a more compact data type.
The order of columns in an index is important. You should use the columns left-to-right in search criteria, joins, or sorting.
Selectivity ratio is a statistic about a database index.
SELECT COUNT(DISTINCT status) / COUNT(status)
AS selectivity
FROM bugs;
The lower the selectivity ratio, the less effective an index is.
If a value appears on many rows in the table, it's better to scan the entire table than using the index.
Some example of queries that can't benefit from index.
CREATE INDEX TelephoneBook ON accounts (lastname, firstname);
SELECT * FROM accounts ORDER BY firstname, lastname;
CREATE INDEX datemonth ON bugs (date_reported);
SELECT * FROM bugs WHERE MONTHS(date_reported) = 4;
EXPLAIN for getting a database analysis.
If an index provides all the columns we need, then we don't need to read rows from the table at all.
Last update: 221 days ago