CMS для потрала с нагрузкой

Пока отцы не разошлись, земной вопрос: не составной индекс на булевых полях (male/female) - зло?

Если столбец участвует в запросе, то на нём должен быть индекс.

Так же важный момент, что если в запросе в условии участвует более 1 столбца, то на всех них должен быть составной индекс в том же порядке как и в запросе.

не составной индекс булевый это типа одна колонка где 2 значения? Ну если в целом у вас там во всех строках одно значение то оно не даст буста

Это не совсем так, точнее это совсем не так =)) в каком бы порядке вы не составили индекс расположение в where полей индекса никак не влияет на его использование

ну да. true и false примерно поровну. Индекс из одного поля, другие колонки в отборе не участвуют и индекс не содержит включенных полей для select"a примерно такого вида:

select field1, field2, ... where indexedfield = 0

И второй момент select count() с аналогичным отбором.

Откуда у меня такие сомнения? При отборе с результатом, скажем 50% строк, сервер сначала перебирает индексы для получения ПК, а потом ищет кластеризованный первичный индекс для получения остальных полей строки. Не дешевле ли сразу сканировать всю таблицу?

Я если честно немного не понимаю про что речь =))) Запрос (простой) может использовать только один индекс. В вашем случае если у вас 50% одно значение и 50% другое, то БД будет перебирать только пол таблицы, но это все еще очень дорого если таблица большая, если конечно у вас фильтр не по единственному этому полю, а то если поле одно то и перебирать он не будет. Скажем так, индекс отрезает от таблицы кусок по которому он будет делать поиск и чем больше этот кусок тем дольше будет поиск

Зачем втирать? Было 300к хостов в сутки, но не забывайте про кеш, возможно это спасало + кф. Проекты которые 300к имеют в сутки, запросто имеют финансы чтобы сделать самопис, кому что.

Я просто не хотел вдаваться в дебри о селективности.

Я всегда в голове держу мысль, что сам запрос уже сделан идеально и просто по нему надо повторить индекс, это как минимум удобно и легко запомнить 😊

Но опять же, если сильно вдаваться, то есть хорошая статья:

https://highload.today/indeksy-v-mysql/

Там рассказано про порядок столбцов в индексах. Там есть разница, но надо смотреть все запросы и тестировать их.

Так там описывается важность последовательности полей при создании составного индекса, а не при выборке по нему =)) Порядок при создании составного индекса влияет на эффективность и это да, но если честно я ни разу не замечал чтобы порядок колонок в запросе влиял на применение индекса, а миф этот почему то постоянно ходит, тогда бы мы прежде чем писать запрос шли бы смотрели как был создан составной индекс.

Да я если честно хрен на это забивал, и просто делаю по порядку, чтобы удобнее было смотреть при составлении этого индекса. Поэтому и писал, что легче просто это.

В реальности мало кто заморачивается этим т.к.

1. Экономия на спичках, когда выполняется ~150 запросов для генерации страницы, там эти копейки не сильно важны на 1 запросе

2. БД сами там внутри совершенствуются и умеют/учатся оптимизировать это всё

3. Время программиста деньги. Сидеть над каждым запросом это слишком затратно, лучше на страницу кэш повесить

Короче это разговор уже более философский.

Решил на практике проверить разницу в скорости запросов. Использовал постгресс 16 с простой таблицей:

create table news
(
    id      serial
        primary key,
    title   varchar(30)  not null,
    content text         not null,
    slug    varchar(128) not null
);

alter table news
    owner to postgres;

create index idx_id_btree
    on news (id);

create index idx_slug_hash
    on news using hash (slug);

create index ix_news_id
    on news (id);

Добавил туда более 100 тысяч записей, и сделал код, ищущий записи по разным полям, обычный селект-where

@timer
    def get_news_by_id(self, n_id):
        stmt = (
            select(News)
            .where(News.id == n_id))
        with Session() as session:

            print("Get by ID"+"="*120)
            print(stmt)
            result = session.execute(stmt).one()
            print("Result:")
            print(result.News.id, result.News.title)
            
            
    @timer
    def get_news_by_slug(self, slug):
        stmt = (
            select(News)
            .where(News.slug == slug))
        with Session() as session:

            print("Get by slug: "+"="*120)
            print(stmt)
            result = session.execute(stmt).one()
            print("Result:")
            print(result.News.id, result.News.title)
            
            
    @timer
    def get_news_by_title(self, title):
        stmt = (
            select(News)
            .where(News.title == title))
        with Session() as session:

            print("Get by title: "+"="*120)
            print(stmt)
            result = session.execute(stmt).one()
            print("Result:")
            print(result.News.id, result.News.title)

И получил интересные результаты:

Get by ID========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.id = :id_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.0937650203704834
Get by ID========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.id = :id_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.006050825119018555
Get by slug: ========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.slug = :slug_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.007730007171630859
Get by slug: ========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.slug = :slug_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.005480051040649414
Get by title: ========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.title = :title_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.015486001968383789
Get by title: ========================================================================================================================
SELECT news.id, news.title, news.content, news.slug 
FROM news 
WHERE news.title = :title_1
Result:
109536 SdGRBUQoZfQDiUh3zTC0wUUzHicUvM
Execution time: 0.012657880783081055

Process finished with exit code 0

результат 3 - по неиндексированному полю title - ожидаемо он на порядок меньше. А вот с айди и слагом интересно. Дело в том что первый запрос по айди, где для индексации используется винарное дерево - сильно просаживается по скорости, второй и последующий - такой же или немного быстрее чем поиск по текстовому полю с индексом в виде хэш
А хэш всегда стабилен по скорости, независимо от количества запросов. 
Кто-то может это обьяснить?