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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Maybe will get help
But please note: if
birthis of typeDATETIME, you are missing most of 12/31.No indexing will help due to the leading wildcard character.
The architecture is poorly designed. Imagine what happens when a cat has a birthday. You must update this column. Instead, use
birthandCURDATE()to do some date arithmetic.But if you do retain the
agecolumn, then this might help:I introduced the
cat.birthquery in another question today; see. See my Index Cookbook for information on how to build an appropriate index for a givenSELECT. It shows whereWHERE ... AND ...you can use "composite" (multiple columns)INDEXto good advantage. None of your examples can use composite indexes.