Select * from table a where a=? And b=? And c=?

Create the following index
A A, c based composite index
B B a separate index
C C a separate index
D, b, c respectively establish a separate index
E, b, c based composite index

Can those options will use index

CodePudding user response:

In theory, all options are possible

CodePudding user response:

Those are bound to

CodePudding user response:

This depends on the data distribution and the amount of data, if there are only a few data, even if is built on the index, the estimate is also take a full table scan

CodePudding user response:

Shall not use, can only be possible, if the statistical information is not accurate, may be less than,

CodePudding user response:

Data quantity is large, such as in the 1000 w, statistical information is accurate, have to collect in before, the premise condition, those who would use the index

CodePudding user response:

The select floor (count (distinct) b/count (*) * 100) from a;

Use this to check the selectivity of three fields, the greater the more close to 100 more suitable for indexed

CodePudding user response:

refer to the second floor oBandari response:

those are bound to

Never say 100%

CodePudding user response:

In other words,,, which is ought to be, don't consider the data volume is small or statistical information, according to the theory,,,

CodePudding user response:

refer to the eighth floor oBandari response:

in other words,,, which is ought to be, don't consider the data volume is small or statistical information, according to the theory,,,

Regardless of the statistics data quantity is not so much, these are the theory, the amount of data data distribution is the most easy to factors that influence the CBO and actually because of different environment, such as statistics and may not be accurate, even under the condition of accurate, COST calculation formula may be out of the moth etc... Said more bad,

In theory, you several indexes of example, is likely to be used, also can use less than,

CodePudding user response:

refer to the eighth floor oBandari response:

in other words,,, which is ought to be, don't consider the data volume is small or statistical information, according to the theory,,,

If at the same time, there is only one index exists, then these indexes are used,

If there are multiple index, refer to # 6,

CodePudding user response:

Nothing will use index,