Choice of columns for indexing

[Gangeya]

Its a situation of slow-running SQL statements, I thought of my indexed tables (most are very large)! My question is: do I create indexes on columns that I have in the WHERE clause or on my ID fields with what I'm doing my knuckles, knowing that my ID columns appear only rarely in WHERE clauses.

[!const]

do I create indexes on columns that I have in the WHERE clause or on my ID fields with what I'm doing my knuckles, knowing that my ID columns appear only rarely in WHERE clauses.

Both in general ...

You should also see:

- What are the most used applications
- What are the columns in the WHERE clauses most discriminating

[Zecho]

A perfect index is an index that takes the WHERE clauses and those joints.

For example,

SELECT * FROM employee e, s Services
WHERE sex ='F' AND date_nassaince between '01.01.1990' AND '31.12.1990'
AND e.srv = s.srv

The perfect index for this query is:
- The file employees -> sex birth_date, srv
- The services file -> srv

[!const]

A perfect index is an index that takes the WHERE clauses and those joints.

For example,

SELECT * FROM employee e, s Services
WHERE sex ='F' AND date_nassaince between '01.01.1990' AND '31.12.1990'
AND e.srv = s.srv

The perfect index for this query is:
- The file employees -> sex birth_date, srv
- The services file -> srv

Attention column "sex" is not a very good choice for an index because of its discriminatory character bit (only two values 'F' or 'H'). In any case, and for DB2 z/OS instance, we must absolutely avoid placing it in the first column of the index, the DBMS is very sensitive to the selectivity of the first column of an index to determine a path (known FIRSTKEYCARD).

[Gangeya]

Attention column "sex" is not a very good choice for an index because of its discriminatory character bit (only two values 'F' or 'H'). In any case, and for DB2 z/OS instance, we must absolutely avoid placing it in the first column of the index, the DBMS is very sensitive to the selectivity of the first column of an index to determine a path (known FIRSTKEYCARD).

Then what do you suggest as an index for the example given by Zecho knowing that speaks DB2/AS400?

[!const]

==>> To the table "Employees" I choose:
Date of Birth
for other columns, I'm skeptical ... column "sex" because it is not discriminating and column "service" because it is not valued in the application ... the worst and the doubts I trials in a qualification (volumetric identical to the production environment)

But, it will probably be another index on the column "service" because it is probably a foreign key

==>> To the table "Services":
choice identical to that of Zecho ... more is probably the primary key

I must add that I do not know DB2 AS/400 but DB2 z/OS ...

[void]

I quite agree with !const to say that an index with first column as a column with only 2 values discriminatory, does not much. Indeed, research on the sex back code 1 line on 2 that are likely to be dispatched on all pages of the table. So SELECT * FROM TABLE WHERE Cdsexe =: HV will play half of the index that will bring it on all pages of the table (1 line of 2 but on every page). In this case, a good old scan of the table with the prefetch will be much faster. Exception: SELECT COUNT (*) FROM TABLE WHERE Cdsexe =: HV. DB2 is then indexonly and simply read the corresponding number of cases, the index is interesting. But in real life, it is seldom content to make count (*).

The choice of index, it is never clear. A few rules:
- An index on each column of a table to predict all possible access, this is heresy.
- When accessing a table with 3 predicates, a multi-column index on 3 columns involved is much more interesting than 3 index on each column.
- Watch the indices unique style where not null. For example, a table of 6 million rows with a column filled in once 2. That means 3 million rows with NULL and 3 million lines OK. If you access this column, an index is needed and respond as needed unless one has the misfortune to write WHERE COL IS NULL. In this case, bof .... But the biggest problem of these indexes is when deleting rows. In fact, DB2 will invalidate the line in the index and why it has the index value, NULL for our case and RID. DB2 will then scan all RIDs by pointing to NULL (it has 3 million) to find the right one. It can last ...
- Do not forget the interest of access indexonly. So when you have a table with few columns, it is often the shot put all the columns in the index. The index is larger, but more accesses to the datas, which is great for perf.
- Watch the little discriminating index: it all comes about the message. A scan is often preferable to going through a bit discriminating index.
- Do not forget that too many indexes on a table, it generates I/O at all insertion and deletion (update even if the column is changed in the index). To take into account when selecting indexes, even if it is true that most tables are read 1000 times more often they are updated. So is the need to sustain SELECT.

And to complicate matters, remember that every rule has its exceptions, so it is valid in a case is not always a case that may yet appear more or less the same!