Блог

У меня есть 2 таблицы, и я делаю запрос объединения, дело в том, что в основной таблице у меня есть 2 столбца с идентификатором, который необходимо заменить полными именами из второй таблицы, поэтому я дважды выполняю запрос по одной и той же таблице. Дело в том, что в нем нет примерно половины записей, если я изменю положение соединений, тогда появится вторая половина. данные в столбцах идентификатора 2 примерно такие: (271, 272), (272, 271)

     query = "SELECT * FROM (SELECT * from transactions {2} LIMIT {3}, {4}) as transactions " 
            "LEFT JOIN (SELECT account_number as dr, account_name as dr_n, acc_id, company_id, deleted FROM tb_accounts) as tb1 ON tb1.acc_id = transactions.dr_acc " 
            "LEFT JOIN (SELECT account_number as cr, account_name as cr_n, acc_id, company_id, deleted FROM tb_accounts) as tb2 ON tb2.acc_id = transactions.cr_acc " 
            "LEFT JOIN (SELECT document as doc_name, doc_id, company_id, deleted FROM documents ORDER BY documents.date DESC) as doc1 ON doc1.doc_id = transactions.document " 
            "and doc1.company_id = {0} and doc1.deleted = 0 " 
            "WHERE tb1.company_id = {0} and tb1.deleted = 0 and " 
            "tb2.company_id = {0} and tb2.deleted = 0 and " 
            "transactions.company_id = {0} and {1} transactions.deleted = 0".format(company_id, filter, sort, sn, en)
  

Ваш запрос выглядит довольно запутанным и может выполняться довольно медленно, создавая временные таблицы для использования в объединениях.

Также я не верю, что вам это нужно LEFT JOIN . Вместо этого должно быть JOIN ( INNER JOIN ) .

И причиной вашей проблемы является оператор ОГРАНИЧЕНИЯ, размещенный сверху.

Я мог бы предложить использовать следующий запрос (посмотрите, как я его вставил в Python — его гораздо удобнее копировать / вставлять и тестировать в клиенте БД):

 query = """
    SELECT *
    FROM transactions as transactions
    JOIN tb_accounts as tb1
    ON tb1.acc_id = transactions.dr_acc AND tb1.deleted = 0 AND tb1.company_id = {0}
    JOIN tb_accounts as tb2
    ON tb2.acc_id = transactions.cr_acc AND tb2.deleted = 0 AND tb2.company_id = {0}
    JOIN documents as doc1
    ON doc1.doc_id = transactions.document
        AND doc1.company_id = {0}
        AND doc1.deleted = 0
    WHERE transactions.company_id = {0}
      AND transactions.deleted = 0
      {1}
    {2}
    LIMIT {3}, {4}
""".format(company_id, filter, sort, sn, en)
  

Для оптимизации скорости приведенного выше запроса были бы полезны следующие индексы:

 CREATE INDEX idx_del_comp_acc
ON tb_accounts (deleted, company_id, acc_id);

CREATE INDEX idx_del_comp_doc
ON documents (deleted, company_id, doc_id);

CREATE INDEX idx_del_comp_dr_acc
ON transactions (deleted, company_id, dr_acc);

CREATE INDEX idx_del_comp_cr_acc
ON transactions (deleted, company_id, cr_acc);
  

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

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

Используйте команду EXPLAIN перед вашим запросом, чтобы узнать, как сервер собирается ее запускать и какие индексы будут использоваться, если таковые имеются.

Однако имейте в виду, что добавление индекса увеличивает время запросов на вставку и ОБНОВЛЕНИЕ. Обычно преимущества наличия индекса в разы перевешивают более медленные вставки.