How to index fields in the database?

Question

SQL

Hello.
I decided to practice and solve the problem. Given a table with a tree of categories:

CREATE TABLE category (
    id integer not null primary key,
    parent_category_id integer references category(id),
    name varchar(100) not null
);

And you need to make the following queries:
To select all top-level categories that begin with “auto” .
Did so

SELECT id, parent_category_id, name FROM category WHERE parent_category_id IS NULL AND name LIKE 'авто%';

For a sample of all categories with no more than three subcategories of the next level (without depth) .
Did so

SELECT id, name, parent_category_id FROM test_270220.category WHERE parent_category_id IN 
    (SELECT parent_category_id 
     FROM test_270220.category
     GROUP BY parent_category_id
     HAVING COUNT(parent_category_id) < 4
    );

On a sample of all categories of the lower level (i.e. without children)
Did this

SELECT cat1.id, cat1.name, cat1.parent_category_id
  FROM test_270220.category AS cat1
  LEFT JOIN test_270220.category AS cat2 ON cat2.parent_category_id = cat1.id
  WHERE cat2.id IS NULL;

Requests work, but there is another item in the task:

Write indexes that will make these queries faster.

I don’t interact with SQL so often, googling I found the following options:

CREATE FULLTEXT INDEX name on test_270220.category(name)

CREATE INDEX idx_category_parent_category_id  ON test_270220.category (parent_category_id)

But after creating the indexes, the query time increased o_O, I assumed that this should not be so :). Tell me what can be connected with?

After such a turn, I began to google, stumbled upon an article on Habré . I already wanted to try to do as advised in the article (i.e. create a second table, store a link to the descendant and ancestor there), but the task does not say that you need to create a second table, etc.

That is why I am in a little confusion, or I did something wrong (that after adding indexes, the query time increases). Or is the task compiled not quite correctly and the creation of query optimization indexes cannot be achieved?

Answer 1

I don’t interact with SQL so often, googling I found the following options:

You need not to google options, but to build indexes specifically for your queries.
First you need to fill the table with a sufficiently large amount of test data so that the DBMS is guaranteed to use indexes when building a query plan, and not choose a full scan. After that, you need to use EXPLAIN ANALYZE SELECT ... to analyze queries, on the basis of which you need to create the necessary indexes.

Answer 2

mmm. well here it is necessary to rewrite 2 requests and to add 2 indexes and to suppress in one request on one index. so it will be right.

But after creating the indexes, the query time increased

95% of the interviewees fall on this question, because they do not know that reading an index is also a reading operation.