mysql Dynamic search query indexing

Question

It's my first project, so I'm sorry I have so many questions.

I'm trying to index to search the above Cat table.

However, I don't know how to apply it because the where clause has multiple cases.

SELECT *
FROM CAT
WHERE birth between '2000-01-01' and '2009-12-31';

SELECT *
FROM CAT
WHERE birth between '2000-01-01' and '2009-12-31'
and NAME like '%blue%';

SELECT *
FROM CAT
WHERE NAME like '%blue%'
AND AGE = 5;

If so, can I create indexes on age, name, and birth respectively? If not, do I have to create (age), (age,name), (age,birth), (age,name,birth).... for every case?

Even after reading the book, I am not sure, so I leave a question. I hope you don't feel bad about it.

i use mysql v8.0 innoDB.

Thank you!

Answer 1

WHERE birth between '2000-01-01' and '2009-12-31'

may be helped by

INDEX(birth)

However note: If birth is of type DATETIME, you have left out most of 12/31.

and NAME like '%blue%';

Because of the leading wildcard, no index will help.

AND AGE = 5

is bad schema design. Think what happens when the cat has a birthday. You have to update that column. Instead do some date arithmetic with birth and CURDATE().

But if you do keep an age column, then this may help:

INDEX(age)

I covered cat.birth queries in another Question today; see it. And see my Index Cookbook for more discussion of how to build a suitable index for a given SELECT. It shows what cases of WHERE ... AND ... can use a "composite" (multi-column) INDEX to good advantage. None of your examples can make use of a composite index.