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

  1. Do I need to duplicate columns in the second index if they already exist in the first index?

  2. Columns for the index wrote everything that is in the query (after the where and order by). Is this right or can you just choose a couple?

  3. The order of the columns was based on counting count(*) for each column and choosing an average value over it. Is it right?

  4. Did I understand correctly that if the sample is used for one field from the database, and our index is composite, then, in this case, the index is not taken into account?

  5. In the sample, if the operator || then the index is not taken into account. Why

  6. Should id_advert be added to indexes?

thank

    1 answer 1

    1) Rather, yes, than not, if the order does not match. Almost unambiguously not, if the longer index contains all the fields from the shorter one, moreover, strictly at the beginning and strictly in the same order.

    2) Incorrectly from the word "absolutely". All you have achieved is that instead of a scan on the table, you received a scan on an index that is slightly more compact. But it works exactly as a compact table, and not at all as an index.

    3) Definitely not.

    4) No.

    5) This is a false statement.

    6) Depends on the queries that you intend to optimize by creating an index.

    • 4. I use such query EXPLAIN SELECT * FROM baza WHERE n_category= 10; n_category is in the index, but the result of the query is NULL. So the index is not taken into account after all? 5. The same as in 4 point - Sarkis Allahverdian
    • one
      For SELECT query * FROM baza WHERE n_category = 10; I need an index on one field n_category. Or a composite index, in which the n_category field is the very first in the list. Any other index will not be used. - Akina