mariadb root@localhost:dyw> create table indextab(id int unsigned not null,lname varchar(20),fname varchar(20),phone varchar(20)); Query OK, 0 rows affected Time: 0.015s
mariadb root@localhost:dyw> desc indextab; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | <null> | | | lname | varchar(20) | YES | | <null> | | | fname | varchar(20) | YES | | <null> | | | phone | varchar(20) | YES | | <null> | | +-------+------------------+------+-----+---------+-------+ 4 rows in set Time: 0.027s
mysql root@localhost:dyw> alter table indextab add index name (lname, fname); You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 0.305s
mariadb root@localhost:dyw> insert into indextab(id,lname,fname,phone) values (1 ,'wang','dy','09123456'),(2,'lin','da','09234561'); Query OK, 2 rows affected Time: 0.005s
mariadb root@localhost:dyw> select * from indextab use index(name) where lname=' wang' and fname='dy'; +----+-------+-------+----------+ | id | lname | fname | phone | +----+-------+-------+----------+ | 1 | wang | dy | 09123456 | +----+-------+-------+----------+ 1 row in set Time: 0.025s
mariadb root@localhost:dyw> select * from indextab ignore index(name) where lnam e='wang' and fname='dy'; +----+-------+-------+----------+ | id | lname | fname | phone | +----+-------+-------+----------+ | 1 | wang | dy | 09123456 | +----+-------+-------+----------+ 1 row in set Time: 0.025s