sql - Using Composite primary key when indexing Mysql

Question

I am using MySQL 8.0

show index from table1 outputs

 table    Non_unique    Key_name   Seq_in_index ColumnName  ... Collation    Cardinality    Index_type
 
table1       0          PRIMARY          1           A             A            6M              B-Tree
table1       0          PRIMARY          2           B             A            6M              B-Tree
table1       0           A_foreign       1           B             A            30K             B-Tree
table1      ..

I've always understood that when we have composite primary key, for your query to use ColumnName = Byou must use ColumnName = A before therefore your query looks something like this:

SELECT *
  FROM table1
 WHERE A >= 10
   AND B >= 10;

But, when I only use column B in where clause it still seems to be using index. It returns data really fast. When I use EXPLAIN statement, it outputs

EXPLAIN
 SELECT *
   FROM table1
  WHERE B >= 10;

id   select_type   table  type   poss_key       key      key_len  ref rows  Extra
1      simple      table1 range  A_foreign   A_foreign    4         104      Using Where

In here it seems to use foreign key, What if there isn't do we need to use PRIMARY key with seq_in_index=1 then seq_in_index=2 to use second primary index?

Answer 1

MySQL recently introduced the skip-scan index optimization. This basically does a range scan for each value of the first key, as explained in the documentation:

However, as of MySQL 8.0.13, the optimizer can perform multiple range scans, one for each value of f1, using a method called Skip Scan

So, the index can be used for the query you are using.