Блог

Основываясь на рекомендации другого разработчика здесь, на stackoverflow, я обновил свой запрос, как показано ниже, но мне все еще нужно его дополнительно оптимизировать. Может ли кто-нибудь подсказать мне, как лучше всего применить индексацию к запросу.

См. Запрос Ниже:

 SELECT a.id, a.user_unique_id, a.loan_location, 
          a.ippis, a.tel_no,
          a.organisation, a.branch, a.loan_agree, 
          a.loan_type, a.appr, a.sold, 
          a.loan_status, a.top_up, a.current_loan, 
          a.date_created, a.date_updated, c.loan_id, c.user_unique_id AS tu_user_unique_id, 
          c.ippis AS tu_ippis, c.top_up_approved, 
           c.loan_type AS tu_loan_type, c.dse, c.status, c.current_loan AS tu_current_loan, 
          c.record_category, c.date_created AS tu_date_created, 
          c.date_updated AS tu_date_updated 
FROM loan_applications_tbl a
LEFT JOIN topup_or_reapplication_tbl c
    ON a.ippis=c.ippis   
WHERE ((c.status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up') 
       AND MONTH(CURRENT_DATE) IN (MONTH(c.date_created), MONTH(c.date_updated) 
       AND YEAR(CURRENT_DATE) IN (YEAR(c.date_created), YEAR(c.date_updated)) 
       AND   c.current_loan='1' )) 
OR ( a.loan_status IN ('pending', 'corrected', 'Rejected', 'Processing', 'Captured', 'Reviewed', 'top up')
     AND MONTH(CURRENT_DATE) IN (MONTH(a.date_created), MONTH(a.date_updated)) )
     AND YEAR(CURRENT_DATE) IN (YEAR(a.date_created), YEAR(a.date_updated)) 
     AND (a.current_loan='1' 
          OR (a.current_loan='0' 
              AND a.loan_status IN('Approved','Closed')))))
 

Время выполнения: 53 с

Количество записей: 11000

использование mysql ОБЪЯСНЯЕТ, как сделать снимок экрана ниже: (Как максимизировать информацию в столбце possible_keys

Я ОБНОВИЛ ДОПОЛНИТЕЛЬНУЮ ИНФОРМАЦИЮ НИЖЕ:

Я использую ИЛИ между c и a по следующим причинам:

1. a является родительской таблицей с 66 столбцами, которая заполняется записями займа, если новая запись a имеет совпадающее/существующее ippis (уникальное поле a ), некоторые столбцы в a которой обновляются/перезаписываются данными из новой записи, в то время как остальные данные в записи вставляются как новые строки c ( ippis не уникальны в таблице c ). Это делается для того, чтобы вести историю всех последующих запросов на получение кредита, не оставляя места для резервирования
2. При извлечении записей мне понадобилось предложение big OR , чтобы я мог проверить оба a и c таблицы для всех экземпляров каждой записи о кредите, где status, date and current_loan столбцы соответствуют параметрам в моем предложении WHERE.
3. a всегда иметь полную запись в нем, но c не всегда есть запись в его, разве есть более кредитных заявок за тот же уникальный идентификатор. a содержится этот «кто счете человек как уникальный идентификатор», и дополнительная / дополнительная информация подробно для первого займа, в дальнейшем, после первого займа «с» будет дополнительно / дополнительные сведения о состоянии реальных кредитных заявок с одинаковым уникальным идентификатором.
4. если «A» создается 12 марта, а новая запись «c» создается 16 марта. Запись «А» также получает последнюю обновленную отметку 16 марта, поскольку у нее есть дочернее вложение, которое оказывает на нее некоторое влияние, в то время как у новой c записи есть собственные созданные и обновленные отметки времени. Обновленное поле будет пустым/нулевым для a записи до тех пор, пока не будут внесены изменения или не появится c запись, Обновленное поле будет пустым/нулевым для c записи до тех пор, пока в запись не будут внесены некоторые изменения c

Я надеюсь, что это понятно

Я все время забываю этот термин, так как он очень редко встречается у меня, но в любом случае ваши индексы нельзя оптимизировать с помощью функции MONTH() и YEAR (), поскольку они являются функциями базовых данных. Применяя ДИАПАЗОН дат, они могут это сделать. Таким образом, вы можете сохранить свой месяц/год, например , если что-то было создано в январе 2021 года и обновлено в марте 2021 года, но, кроме того, добавив "and c.date_created >= current_date AND current_date <= c.date_updated" , вы МОЖЕТЕ использовать индекс, если в нем указана дата создания (в данном случае менее важно для даты обновления. Аналогично для вашего другого стола.

Кроме того, когда у вас есть левое соединение из таблицы «а» в таблицу «с», а затем применяете «где», это похоже на то, что вы пытаетесь принудительно присоединиться, но остаетесь левым соединением из-за ИЛИ.

Я бы переместил условие на основе «c» в левое соединение, а затем просто проверил найденную там запись как нулевую или нет.

Хотя и неясно (не было разъяснено, когда я спрашивал), я ДУМАЮ, что при создании новой записи «А» система может фактически указать дату создания как в дату создания, так и в дату обновления. ЕСЛИ ЭТО ТАК, то нам нужно только запросить/указать в поле даты последнего обновления текущий месяц/год деятельности. Теперь это ОСНОВНОЕ требование для предложения where-НЕЗАВИСИМО от основы ИЛИ условия таблицы «C».

Кроме того, поскольку месяц() и год() не оплачиваются (спасибо Олли), я делаю предварительный запрос, чтобы получить начало текущего месяца и следующего месяца, чтобы я мог построить

 WHERE > beginning of this month and LESS than beginning of next month
 

Что касается индексов, я бы начал обновление до

 loan_applications_tbl ( date_created, date_updated, loan_status, current_loan, ippis )
topup_or_reapplication_tbl ( ippis, status, current_loan, date_created, date_updated )
 

Последний запрос, с которым нужно попробовать.

 SELECT 
        a.id, 
        a.user_unique_id, 
        a.loan_location, 
        a.ippis, 
        a.tel_no,
        a.organisation, 
        a.branch, 
        a.loan_agree, 
        a.loan_type, 
        a.appr, 
        a.sold, 
        a.loan_status, 
        a.top_up, 
        a.current_loan, 
        a.date_created, 
        a.date_updated, 
        c.loan_id, 
        c.user_unique_id tu_user_unique_id, 
        c.ippis tu_ippis, 
        c.top_up_approved,
        c.loan_type tu_loan_type, 
        c.dse, 
        c.status, 
        c.current_loan tu_current_loan,
        c.record_category, 
        c.date_created tu_date_created,
        c.date_updated tu_date_updated 
    FROM 
        -- this creates inline mySQL variables I can use for the WHERE condition
        -- by doing comma after with no explicit join, it is a single row
        -- and thus no Cartesian result, just @variables available now
        ( select 
                -- first truncating any TIME portion by casting to DATE()
                @myToday := date(curdate()),
                @howFarBack := date_sub( @myToday, interval 6 month ),
                -- now subtract day of month -1 to get first of THIS month
                @beginOfMonth := date_sub( @myToday, interval dayOfMonth( @myToday ) -1 day ),
                -- and now, add 1 month for beginning of next
                @beginNextMonth := date_add( @beginOfMonth, interval 1 month ) ) SqlVars,

        loan_applications_tbl a
    
            LEFT JOIN topup_or_reapplication_tbl c
                ON  a.ippis = c.ippis   
                AND c.current_loan='1'
                AND c.status IN ('pending', 'corrected', 'Rejected', 
                                'Processing', 'Captured', 'Reviewed', 'top up') 
                AND 
                (
                        (@beginOfMonth <= c.date_created 
                    AND c.date_created < @beginNextMonth)
        
                OR
                        (@beginOfMonth <= a.date_updated 
                    AND a.date_updated < @beginNextMonth )
                )

    WHERE
            -- forces only activity for the single month in question
            -- since the "a" table knows of any "updates" to the "C",
            -- its updated basis will keep overall restriction to any accounts

            -- updated within this month in question only
            -- testing specifically for created OR updated within the
            -- current month in question

        a.date_created >= @howFarBack
        AND
            (
                    (@beginOfMonth <= a.date_created 
                AND a.date_created < @beginNextMonth)
        
            OR
                    (@beginOfMonth <= a.date_updated 
                AND a.date_updated < @beginNextMonth )
            )
        
        -- and NOW we can easily apply the OR without requiring
        -- to run against the ENTIRE set of BOTH tables.
        AND (
                    c.ippis IS NOT NULL
                OR 
                    ( a.loan_status IN (  'pending', 'corrected', 'Rejected', 'Processing', 
                            'Captured', 'Reviewed', 'top up')
                    AND (   
                            a.current_loan = '1' 
                        OR  (   a.current_loan = '0' 
                            AND a.loan_status IN ('Approved', 'Closed')
                            )
                        )
                    )
            )
 

ЗАКРЫТИЕ КОММЕНТАРИЕВ ДЛЯ ЗАПРОСА

Я изменил запрос, а также основной индекс в первой таблице, чтобы ВКЛЮЧИТЬ (первую позицию) дату создания записи. Я также добавил дополнительную переменную @howFarBack, чтобы указать максимальное время возврата для рассмотрения вопроса о кредите. Я объявил дефолт 6 месяцев назад. Вам когда-нибудь понадобится рассмотреть данный счет старше 6 месяцев для получения кредита? Или в записи учетной записи «а» есть что-то, что можно было бы включить на 10 лет назад и захотеть включить? У меня сложилось впечатление, что это новая дата добавления КРЕДИТНОЙ ЗАЯВКИ. Если это так, то разрешение вернуться на 6 месяцев назад до того, как оно будет одобрено, доработано, отменено, все равно предотвратит прохождение данных за столько месяцев в прошлом.

В предложении WHERE я добавил явное добавление для CREATED_DATE >= @howFarBack. Невозможно было бы создать дочернюю запись, не говоря уже об обновлении в любое время до первоначальной даты добавления. Это приведет к тому, что для квалификации будет использоваться только активность в текущем месяце ИЛИ В БУДУЩЕМ.

Пример: Создайте кредит 28 апреля. Таким образом, при выполнении запроса начало месяца-1 апреля, но МЕНЬШЕ, чем 1 мая (это позволяет включить 30 апреля в 11:59:59 вечера).

Теперь мы вступаем в май, и изменение по кредиту будет произведено 4 мая. Мы находимся в новом месяце, и @howFarBack по-прежнему позволяет более старым заявкам до декабря 2020 года, возможно, претендовать на всю таблицу заявок, которая, насколько нам известно, может датироваться 2005 годом. Вы всегда располагаете самыми актуальными данными, и вы можете достаточно легко изменить @howFarBack в качестве максимального времени возврата. Это должно помочь вашим потребностям в производительности.