MySQL leftmost prefix (leftmost Prefix) & Combined Index (composite index, Multi-column index)

Tag:body   rom   order    combination    table    gpo   ble    composite    int   

The

Data is from the network and is for reference only.

CREATE TABLE Test (
a int,
B int,
C int,
KEY idx (a,b,c)
);

Excellent: SELECT * from Test WHERE a=10 and b>50;
Poor: SELECT * from Test WHERE b>50;

Excellent: SELECT * from Test ORDER by A;
Poor: SELECT * from Test ORDER by B;
Poor: SELECT * from Test ORDER by C;

Excellent: SELECT * from Test WHERE a=10 ORDER by A;
Excellent: SELECT * from Test WHERE a=10 ORDER by B;
Poor: SELECT * from Test WHERE a=10 ORDER by C;

Excellent: SELECT * from Test WHERE a>10 ORDER by A;
Poor: SELECT * from Test WHERE a>10 ORDER by B;
Poor: SELECT * from Test WHERE a>10 ORDER by C;

Excellent: SELECT * from Test WHERE a=10 and b=10 ORDER by A;
Excellent: SELECT * from Test WHERE a=10 and b=10 ORDER by B;
Excellent: SELECT * from Test WHERE a=10 and b=10 ORDER by C;

Excellent: SELECT * from Test WHERE a=10 and b=10 ORDER by A;
Excellent: SELECT * from Test WHERE a=10 and b>10 ORDER by B;
Poor: SELECT * from Test WHERE a=10 and b>10 ORDER by C;

MySQL leftmost prefix (leftmost Prefix) & Combined Index (composite index, Multi-column index)