Composite primary and cardinal

Question

I have some questions about composite primary keys and column power. I searched the Internet but could not find a definitive answer, so I try again. Questions:

Context: Large (rows 50M - 500M) Prep OLAP tables, not NOSQL, not Columnar. MySQL and DB2

1) Does the order of the keys in PK?

2) If the power of the speakers varies greatly, what should be used first. For example, if I have CLIENT / CAMPAIGN / PROGRAM, where the CLIENT is very cardinal, CAMPAIGN is moderate, PROGRAM is almost like a raster index, which order is better?

3) What is the best order for Join if there is a Where clause and when there is no Where clause (for views)

Thanks in advance.

Answer 1

You have MySQL and DB2. This answer is for DB2, MySQL does not have this.

Yes, of course, this is logical, but the optimizer takes into account much more than just that.

Typically, the order of the columns in the WHERE clause (join) does not matter (and should not).

However, there are two elements related to the predicate order that may be the cause of your question.

• What will be the order of the columns in the index against which the WHERE clause is processed. Yes, there it is best to specify the columns in order of highest power to lowest. This allows the optimizer to focus on a smaller range of strings.

  • (). , .
    .

• , ( ), , , , . Join Transitive Closure , , , , , ( UPDATE STATS ).

  , 100 _A 1 000 000 _B , A: B, B: A. IOPS, - .

  - :

  • , (1). , , .

  • ,

  • . -. ( ).

  • , , . , .

  • , , , , , SET JTC OFF ( DB2), , WHERE. /.

  • . , . .

Answer 2

1) PK?

, , PRIMARY KEY.

2) , . , CLIENT/CAMPAIGN/PROGRAM, , CAMPAIGN , PROGRAM , ?

, . , ; , .

, .

3) Join, Where Where ( )

, WHERE.