Можно ли сделать индекс по дате desc ,а не asc?

Question

[Qwyu_4iLofK]

Доброго дня,
очень нужно создать индекс по дате desc (мне нужно рассчитывать на 90 дней вперед , каждый день по предыдущей дате), поэтому решение этой задачи очень важно, замедленность идет с ужасающей прогрессией с каждым днем:
1)Написала перед сущностью:

@ORM\Table(indexes={
 *     @ORM\Index(name="date_idx", columns={"date_mod"})})

создает индекс с ASC.
2)пыталась в миграции прописать принудительно DESC
все равно видно в пхпшторме , что индекс с ASC
3) прямой запрос в ппхпшторм в бд, -> индекс с asc.
4) даже в админке (adminer.php) если сделать прямой запрос , индекс создается с asc.
что делать , позарез не знаю.

Прямой запрос такой:
CREATE INDEX date_idx ON mod (date_mod DESC)

Comments

[FanatPHP]

Я не понял какая связь между "индекс по дате desc" и "мне нужно рассчитывать на 90 дней вперед"
можно пояснить?

[Qwyu_4iLofK]

сначала я заполняю данные за сегодняшнее число 07.02, далее я заполняю за завтрашнее число 08.02 на основе данных за сегодняшнее число 07.02, и тд.
ясно что мне нужен индекс даты desc( чтобы искало сначала 08.02, 07.02)

[Алексей Романенко]

mmm45 какая версия mysql?

[FanatPHP]

это какая-то чушь
чтобы заполнить за завтрашнее число, не нужен индекс по убыванию.
что значит "заполняю"?
можно, в конце концов посмотреть собственно "тормозящий" SQL и его EXPLAIN?

[FanatPHP]

у меня вообще складывается такое ощущение, что в БД пишутся именно такие циферки - 08.02, 07.02, в поле varchar
и поэтому, блин и нужен обратный индекс

[Akina]

очень нужно создать индекс по дате desc (мне нужно рассчитывать на 90 дней вперед , каждый день по предыдущей дате), поэтому решение этой задачи очень важно, замедленность идет с ужасающей прогрессией с каждым днем:

1) DESC в принципе не нужен. Индекс одинаково эффективен для поиска как в прямом, так и в обратном направлении. Ибо с точки зрения компьютера нет никакой разницы между +1 и -1.

2) После того - ещё не значит вследствие того. То, что скорость поиска уменьшается с ростом количества записей, вряд ли связано с "не тем" направлением сортировки в индексе. Скорее всего основная проблема в снижении селективности.

3) Задавать вопрос о производительности запроса, но при этом не показать ни структур таблиц, ни текста запроса, ни плана его исполнения? Из откуда мы должны выдумать свои советы - на потолке подсмотреть? из пальца высосать? дурь какая-то...

FanatPHP,

у меня вообще складывается такое ощущение, что в БД пишутся именно такие циферки - 08.02, 07.02, в поле varchar

Не видя ни структуры данных, ни примера наполнения, сказать что-то определённое нельзя. Если поле строковое, а формат хранения показанно-дебильный - то почему и нет? у MySQL с фантазией вообще плохо, какую дурь у него попросят, такую он и делает.

[Qwyu_4iLofK]

Алексей Романенко, версия mysql:5.6.51, на 8 нельзя перейти, хостинг не позволяет.

[Qwyu_4iLofK]

FanatPHP, Akina то есть я добавляю в бд записями-расчетами , основанными на прошлой дате. По мере роста таблицы идет тормоз. В основном использую join, но тормозить никак не должно.(есть foreign ключи)
Работаю в связке php-doctrine(createQueryBuilder('m')).
Я очень сильно хочу разобраться, напишите, пожалуйста, мануал выявления таких проблемных мест., или как погуглить. Оговорюсь, что идет c каждым запросом
SELECT * from mod where date_mod="предыдущая дата" с джойнами из других таблиц, отсортированных по трем полям. У поля 'date_mod' тип datetime.

[FanatPHP]

mmm45, вам два человека написали уже, что надо не на пальцах показывать, а привести здесь конкретные
структуры таблиц
SQL запросы
EXPLAIN этих запросов
Где это всё?

С какой стати вы решили, что для запроса SELECT * from mod where date_mod="предыдущая дата" нужен обратный индекс? здесь нет никакой сортировки, здесь обращение по константному значению. КАКАЯ РАЗНИЦА, как будет отсортирован индекс, если мы ищем константу?
какая вообще разница, какая именно дата стоит в условии? вчерашняя, послезавтрашняя или через год? это все равно будет константное значение.

[FanatPHP]

если тип datetime то как вы ищете по дате? совпадений же практически не будет?
почему вы пишете какое-то детское 08.02 вместо нормального формата в котором хранится дата?
почему вы никогда не пишете конкретно, нормальный исполняемый SQL, а показываете какие-то огрызки, "вот здесь вчерашняя дата"?

[Qwyu_4iLofK]

Akina, спасибо большое за развернутый ответ, я так и подумала, наверное, не играет роли был создан индекс по desc, или asc. только у меня версия 5.7

2) После того - ещё не значит вследствие того. То, что скорость поиска уменьшается с ростом количества записей, вряд ли связано с "не тем" направлением сортировки в индексе. Скорее всего основная проблема в снижении селективности.

в снижении селективности, эту фразу не очень поняла.

3) Задавать вопрос о производительности запроса, но при этом не показать ни структур таблиц, ни текста запроса, ни плана его исполнения? Из откуда мы должны выдумать свои советы - на потолке подсмотреть? из пальца высосать? дурь какая-то...

Простите, я думала, что проблема в том, что у меня индекс не создается должным образом , а именно по desc. Вот задала соответствующий вопрос. И благодаря Вам получила ответ,что роли не играет, по какой сортировке был создан индекс. Спасибо большое, считай уже полдела сделано.

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

Я думаю, что нужно
1. Вытащить из доктрины (createQueryBuilder) и перевести в mysql
2. Анализировать каждый запрос с помощью explain

и все? то есть погуглить по поводу explain.

ЗЫ: Если вообще не разберусь, попозже опишу более подробно структуру. И это понятно, будет другой вопрос.

[Akina]

в снижении селективности, эту фразу не очень поняла.

Например, если селективность индекса 100 (условию в среднем соответствует только одна запись из сотни), использование индекса даст заметный прирост к скорости выполнения запроса. Но если селективность 10 (условию в среднем соответствует только одна запись из десятка), то использование того же индекса скорее всего станет не только неэффективным, но и вредным, а отказ от использования индекса и использование прямого сканирования таблицы даст более быстрое выполнение.

Просто при использовании индекса надо читать и индекс, и таблицу (причём последнюю - рандомным чтением). А чтение с диска - это самая медленная из всех операций выполнения запроса.

[Алексей Романенко]

mmm45, в коментах уже ответили, сортировка по desc появилась в 8 версии. Ну и уже ответили много про решение задачи

Answer 1

[Василий Банников]

Судя по всему, нельзя:
https://github.com/doctrine/orm/issues/8128
В документации и на SO также не нашёл.

Comments

[Алексей Романенко]

Но второй комент говорит как это можно сделать. Просто исправьте миграцию как вам нужно и доктрина не будет потом ничего исправлять.

[Qwyu_4iLofK]

Василий Банников, я повторюсь, пробовала.
смотрите п.2 вопроса, не помогло.
и этот пост я видела.

[Qwyu_4iLofK]

Алексей Романенко, п. 2 смотрите, пробовала, никак.

[Akina]

MySQL 8.0 Reference Manual / .. / CREATE INDEX Sta...:

A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. ASC and DESC are also not supported for multi-valued indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

Так что ждите, когда доктринёры поспеют за возможностями СУБД. Есть ещё вариант - введение вычисляемого (и индексированного) поля, вроде как это-то поддерживается.

[Василий Банников]

mmm45, каким образом п2 делала?

[Василий Банников]

Akina, понятно дело, что сырым SQL что угодно можно сотворить.
Речь о том, что в ормке нет встроенного синтаксиса для такого.

[Qwyu_4iLofK]

mmm45,

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20220207185432 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        $this->addSql('DROP INDEX date_idx ON mod');
        $this->addSql('CREATE INDEX date_idx ON mod (date_mod DESC) ');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP INDEX date_idx ON mod');
    }
}

дальше php bin/console doctrine:migrations:migrate

Также делала 'CREATE INDEX date_idx ON mod (date_mod DESC)' прямым запросом в бд.

[Василий Банников]

mmm45, и какая ошибка?

[Qwyu_4iLofK]

Akina, я вижу минус Доктрины, в том что невозможно сделать вложенные запросы и обьединение запросов, а в laravel это можно сделать?

[Qwyu_4iLofK]

Василий Банников, в самом деле никакой нет ошибки, миграция идет, а в бд на базе пхпшторма по-прежнему asc.

[Василий Банников]

mmm45, если что, phpstorm автоматически информацию о схеме не обновляет)

[Дмитрий Гординский]

mmm45, как уже сказали выше - шторм кеширует структуру базы, так что правильность индексов лучше проверять запросами.

[Qwyu_4iLofK]

Василий Банников, Дмитрий Гординский нее...
если я сделаю запрос типа SHOW INDEXES from mod
collation у данного индекса по прежнему A.-((((( то есть asc.