Оптимизировать этот запрос для 1000 пациентов в таблице пациентов

Низкая производительность в любой системе RDBM обусловлена ​​плохим дизайном. В своей скрипте SQL вы сохраняете числа в поле varchar. Ваш текущий движок таблиц - MyISAM, который не поддерживает отношения !? No relationships => no index => no quick lookups

Чтобы улучшить производительность, я предлагаю вам изменить дизайн стола. Таблица счетов может иметь следующие изменения. Change Engine to InnoDB и

ALTER TABLE `invoice` ENGINE = Innodb;

ALTER TABLE `invoice` 
CHANGE COLUMN `patientid` `patientid` INT(12) UNSIGNED NOT NULL ,
CHANGE COLUMN `ammount` `ammount` DECIMAL(14,2) NOT NULL ,
CHANGE COLUMN `discount` `discount` DECIMAL(3,2) NULL DEFAULT '0' ,
ADD INDEX `fk_invoice_patient_idx` (`patientid` ASC);
ALTER TABLE `invoice` 
ADD CONSTRAINT `fk_invoice_patient`
  FOREIGN KEY (`patientid`)
  REFERENCES `test`.`patient` (`patientid`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

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

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

SELECT patientid, firstname,lastname,mobilephone,email, FORMAT( COALESCE(
    ( SELECT SUM(ammount)-SUM((ammount * (discount/100))) 
    FROM invoice 
    INNER JOIN patient
    ON invoice.patientid = patient.patientid 
    and invoicednumber >0) - 
    ( SELECT SUM(ammount) 
      FROM payment 
      INNER JOIN patient 
      ON payment.patientid = patient.patientid ),0),0) AS answer, 
    DATE_FORMAT(( SELECT max(paymentdate) 
                  FROM payment 
                  INNER JOIN patient 
                  ON payment.patientid = patient.patientid ),'%d-%m-%Y') As lastpaymentdate 
from patient WHERE 1 

Это будет работать быстрее !!!!

Попробуйте и дайте мне знать

ОБНОВЛЕНИЕ
Я заменил предложение WHERE на внутреннее соединение, которое поможет быстро получить данные .
Я также добавил бы, что если индексы отсутствуют по первичному ключу, тогда добавьте.

Хотя пример, ваше предложение WHERE в основном возвращает ВСЕХ пациентов. Вероятно, что убивает вашу производительность, так это то, что вы делаете 3 коррелированных запроса в своих списках полей, каждый раз запрашивая у каждого человека. Один раз для выставления счета и два раза для платежей.

Вместо этого я реструктурировал запрос на основе Pre-Query LEFT-JOIN в таблицы счетов и платежей. Если вы посмотрите, таблица счетов-фактур предварительно суммируется с GROUP BY каждого идентификатора пациента. Аналогично по таблице платежей GROUP BY каждого пациента. Таким образом, в худшем случае каждый подзапрос будет возвращать по САМОЙ одной записи для данного пациента с суммой всех счетов и соответствующих скидок. Для платежей - сумма всех платежей И самая последняя дата.

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

SELECT 
        p.patientid, 
        p.firstname, 
        p.lastname, 
        p.mobilephone, 
        p.email,
        coalesce( PatInv.JustSumOfAmount - PatInv.DiscountedAmounts, 0 )
            - coalesce( PatPay.PaidAmounts, 0 ) Answer,
        case when PatPay.PatientID IS NULL
            then ' '
            else Date_format( PatPay.LastPaymentDate, '%d-%m-%Y') end lastpaymentdate
    FROM 
        patient p
            LEFT JOIN
            ( select 
                    i.patientID,
                    sum( i.ammount ) justSumOfAmount,
                    sum( i.ammount * ( discount / 100 )) as discountedAmounts
                from
                    invoice i
                where
                    i.invoicedNumber > 0
                group by
                    i.patientID
                order by
                    i.patientid ) PatInv
                on p.patientid = patInv.patientID
            LEFT JOIN
            ( SELECT
                    pay.patientID, 
                    Sum(pay.ammount) paidAmounts,
                    max( pay.paymentDate ) LastPaymentDate
                FROM
                    payment pay
                group by
                    pay.patientID 
                order by
                    pay.patientid ) PatPay
                on p.patientID = patPay.PatientID

Я разместил этот запрос на SQL Fiddle Кроме того, просматривая ваши таблицы, таблицы счетов-фактур и счетов-фактур ДОЛЖНЫ КАЖДЫЙ иметь индекс идентификатора пациента, чтобы помочь оптимизировать запросы. Я не знаю, был ли это пример недосмотра с вашего sql-fiddle vs production, но это тоже повлияло бы на производительность.

У меня есть опция SQLFiddle, которая занимает 5 мс против ваших 2-3 мс, но это только для нескольких предоставленных записей. Я бы поспорил, что против ваших 1000+ пациентов и 1000 транзакций в каждом счете и таблице платежей это сделает этот вариант запроса быстрее, чем коррелированные запросы.

Думайте о своем запросе как об этом. У меня 1000 пациентов. Запросите таблицу счетов 1000 раз, запросите таблицу платежей 2000 раз (1000 для суммы платежей, еще 1000 раз для самой последней даты на пациента).

Мой запрос - предварительно запросите таблицу счетов и суммируйте 1 запись на пациента. Предварительно запросите таблицу платежей и суммируйте 1 запись на пациента. Присоедините пациентов к этим двум подтаблицам напрямую по ID.