getNumericIndex picks up indices according to the following priority: primary key > unique key with the smallest count > key with the max cardinality primary key with multi cols is before unique key with single col because we will sort result by primary keys
(tctx *tcontext.Context, db *BaseConn, meta TableMeta)
| 660 | // primary key > unique key with the smallest count > key with the max cardinality |
| 661 | // primary key with multi cols is before unique key with single col because we will sort result by primary keys |
| 662 | func getNumericIndex(tctx *tcontext.Context, db *BaseConn, meta TableMeta) (string, error) { |
| 663 | database, table := meta.DatabaseName(), meta.TableName() |
| 664 | colName2Type := string2Map(meta.ColumnNames(), meta.ColumnTypes()) |
| 665 | keyQuery := fmt.Sprintf("SHOW INDEX FROM `%s`.`%s`", escapeString(database), escapeString(table)) |
| 666 | results, err := db.QuerySQLWithColumns(tctx, []string{"NON_UNIQUE", "SEQ_IN_INDEX", "KEY_NAME", "COLUMN_NAME", "CARDINALITY"}, keyQuery) |
| 667 | if err != nil { |
| 668 | return "", err |
| 669 | } |
| 670 | type keyColumnPair struct { |
| 671 | colName string |
| 672 | count uint64 |
| 673 | } |
| 674 | var ( |
| 675 | uniqueKeyMap = map[string]keyColumnPair{} // unique key name -> key column name, unique key columns count |
| 676 | keyColumn string |
| 677 | maxCardinality int64 = -1 |
| 678 | ) |
| 679 | |
| 680 | // check primary key first, then unique key |
| 681 | for _, oneRow := range results { |
| 682 | nonUnique, seqInIndex, keyName, colName, cardinality := oneRow[0], oneRow[1], oneRow[2], oneRow[3], oneRow[4] |
| 683 | // only try pick the first column, because the second column of pk/uk in where condition will trigger a full table scan |
| 684 | if seqInIndex != "1" { |
| 685 | if pair, ok := uniqueKeyMap[keyName]; ok { |
| 686 | seqInIndexInt, err := strconv.ParseUint(seqInIndex, 10, 64) |
| 687 | if err == nil && seqInIndexInt > pair.count { |
| 688 | uniqueKeyMap[keyName] = keyColumnPair{pair.colName, seqInIndexInt} |
| 689 | } |
| 690 | } |
| 691 | continue |
| 692 | } |
| 693 | _, numberColumn := dataTypeInt[colName2Type[colName]] |
| 694 | if numberColumn { |
| 695 | switch { |
| 696 | case keyName == "PRIMARY": |
| 697 | return colName, nil |
| 698 | case nonUnique == "0": |
| 699 | uniqueKeyMap[keyName] = keyColumnPair{colName, 1} |
| 700 | // pick index column with max cardinality when there is no unique index |
| 701 | case len(uniqueKeyMap) == 0: |
| 702 | cardinalityInt, err := strconv.ParseInt(cardinality, 10, 64) |
| 703 | if err == nil && cardinalityInt > maxCardinality { |
| 704 | keyColumn = colName |
| 705 | maxCardinality = cardinalityInt |
| 706 | } |
| 707 | } |
| 708 | } |
| 709 | } |
| 710 | if len(uniqueKeyMap) > 0 { |
| 711 | var ( |
| 712 | minCols uint64 = math.MaxUint64 |
| 713 | uniqueKeyColumn string |
| 714 | ) |
| 715 | for _, pair := range uniqueKeyMap { |
| 716 | if pair.count < minCols { |
| 717 | uniqueKeyColumn = pair.colName |
| 718 | minCols = pair.count |
| 719 | } |
no test coverage detected