A couple of questions on SQL indexes?

Question

MySQL

Good time. The database has two composite indexes for selecting ads: all ads and VIP (premium).
Questions:

1. Do I need to duplicate columns in the second index if they are already in the first index?
   
2. The columns for the index wrote everything that is in the query (after where and order by). Is this correct or can I just choose a couple?
   
3. The order of the columns was based on the count (*) for each column and chose the average value for it. Is it right?
   
4. Did I understand correctly that if the selection is used for one field from the database, and we have a composite index, then, in this case, the index is not taken into account?
   
5. In the selection, if the || operator is used , then the index is ignored. Why ?
   
6. Do I need to add id_advert to indexes?
   

Thanks for the detailed answers

Answer 1

In order to correctly answer your questions, you need to understand the specifics of your project, what is done in it, etc. Therefore, either hire an experienced person who can advise you, or figure out how indexes are arranged and work (by the way, this is a good and understandable article ). I also recommend using Explain