Knowledge about indexing Cardinality

1. Cardinality simply, the number of unique values of your indexed columns, if the composite index is the only number of combinations.
2. This value will be used as a basis for the MySQL optimizer to determine the statement execution plan. If uniqueness is too small, the optimizer will assume that the index does not do much to help the statement, rather than using the index.
3. The larger the cardinality value means that the more data is used by the index, and the more efficient the execution is.

Let me give you a few examples: A, B, C here is the name of the column A,b,c 1,1,1 1,1,2 1,2,1 1,2,2 2,1,1 2,1,2 2,2,1 2,2,2 If you index a column, its cardinality is 2, because this column has only 1 and 22 values. If you make a composite index of the A,b column, then its cardinality is 4, because the two columns are (1,2), (1,1), (2,1), (2,2) These kinds of combinations. If you make a composite index of a,b,c, then its cardinality is 8 ...

Explanation of official documents:
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk-a. Cardinality's counted based on statistics stored as integers, s o The value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the ' index when doing joins.

To summarize the value of the cardinality column has the following characteristics:

1. The column value represents the number of unique values stored in this column (if the primary key is the number of rows in the record)
2. The column value is only an estimate and is not accurate. (This value is not too accurate in the actual example of the InnoDB database)
3. Column values are not automatically updated and need to be updated by analyze table.
4. The size of the column value affects whether or not you choose this index when you join.
5. When the index is first built, the value of the table cardinality of the MyISAM table cardinality is approximately the number of rows that are null,innodb.
6. MyISAM and InnoDB are different in terms of cardinality calculations.

Here are the results of my test

Select COUNT (*) from Net_user;

20761

Select COUNT (Distinct userId) from Net_user;

20761

Show index from Net_user;

column_name

	Table	non_unique	key_name	seq_in_index		Collation	cardinality	sub_part	Packed	Null	index_type	Comment	index_comment
	Net_user	0	PRIMARY	1	Userid	A	20587				Btree
	Net_user	0	Username	1	Username	A	20587				Btree
	Net_user	1	Lastlogin	1	Lastlogin	A	20587				Btree
	Net_user	1	Doctorid	1	Doctorid	A	20587				Btree

Analyze local table net_user;

Nethospital.net_user

Analyze

Status

Ok

Show index from Net_user;

Net_user

0

PRIMARY

1

Userid

A

20624

Btree

Net_user

0

Username

1

Username

A

20624

Btree

Net_user

1

Lastlogin

1

Lastlogin

A

20624

Btree

Net_user

1

Doctorid

1

Doctorid

A

20624

Btree

The index is fixed and the query efficiency is improved.

Note that if Binlog is turned on, the results of analyze table are also written to Binlog, where we can add keyword local cancellation between analyze and table.