minte9
LearnRemember   ANTIPATTERNS





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.



Questions and answers:
Clink on Option to Answer




1. An index is like ...

  • a) a telephone book
  • b) an intersection table

2. No SQL standards for indexes

  • a) true
  • b) false

3. The order of columns in an index ...

  • a) is important
  • b) doesn't matter

4. In search queries you should use ...

  • a) left-to-right columns from index
  • b) all columns from index

5. Selectivity ratio is low, the index is ...

  • a) less efective
  • b) more efective

6. For database index analysis use

  • a) EXPLAIN
  • b) DESCRIBE


References: