Indexing of dynamic search queries based on MySQL

Question

This is my first project so I'm sorry I have a lot of questions.

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

However, I don't know how to apply it because there are multiple cases for where clause.

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 possible, can I create separate indexes for age, name and birth? If not, do I have to create (age), (age, name), (age, birth), (age, name, birth)... for each case?

Even after reading the book, I'm not sure, so I'm left with a question. Hope you don't feel bad about this.

I use mysql v8.0 innoDB.

Thanks!

Answer 1

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

Maybe will get help

INDEX(birth)

But please note: if birth is of type DATETIME, you are missing most of 12/31.

and NAME like '%blue%';

No indexing will help due to the leading wildcard character.

AND AGE = 5

The architecture is poorly designed. Imagine what happens when a cat has a birthday. You must update this column. Instead, use birth and CURDATE() to do some date arithmetic.

But if you do retain the age column, then this might help:

INDEX(age)

I introduced the cat.birth query in another question today; see. See my Index Cookbook for information on how to build an appropriate index for a given SELECT. It shows where WHERE ... AND ... you can use "composite" (multiple columns) INDEX to good advantage. None of your examples can use composite indexes.