CODEWITHSUNDEEP
mysqlsql-like
M.Alfisyah Reza Daulay
M.Alfisyah Reza Daulay

Reputation: 292

How to improve index usage in queries with LIKE

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    KEY `first_name` (first_name),
    KEY `last_name` (last_name),
    KEY `last_first` (last_name,first_name)
);

Is it good if i have table index in like that if i have queries like :

SELECT * FROM test WHERE first_name = 'A';
SELECT * FROM test WHERE last_name= 'A';
SELECT * FROM test WHERE first_name like '%A%';
SELECT * FROM test WHERE last_name like '%A%';
SELECT * FROM test WHERE last_name= 'A' AND first_name = 'A';
SELECT * FROM test WHERE last_name like '%A%' AND first_name  like '%A%';
SELECT * FROM test WHERE last_name= 'A' OR first_name = 'A';
SELECT * FROM test WHERE last_name like '%A%' first_name  OR like '%A%';

UPDATE WRONG QUERY.

Upvotes: 0

Views: 55

Answers (1)

jdog
jdog

Reputation: 2549

SELECT * FROM test WHERE first_name = 'A';

Index on first_name will help

SELECT * FROM test WHERE last_name= 'A';

Index on last_name will help

SELECT * FROM test WHERE first_name like '%A%';
SELECT * FROM test WHERE last_name like '%A%';

No index will help, you can only optimise for 'A%' or for '%A' with a column of reversed values.

SELECT * FROM test WHERE last_name= 'A' AND first_name = 'A';

Index on lastname,firstname or firstname,lastname will help, but probably on one of them is enough

SELECT * FROM test WHERE like '%A%' AND like '%A%';
SELECT * FROM test WHERE like '%A%' OR like '%A%';

not a valid query

SELECT * FROM test WHERE last_name= 'A' OR first_name = 'A';

Index on either lastname or firstname can help, but good chance of table scan

Upvotes: 1

Related Questions