Блог

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

Вот запрос

 SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name,
(SELECT COUNT(*)
    FROM supplier_questions q1
    WHERE c.supplier_id = q1.supplier_id AND q1.incomplete = '0') AS questions, 
IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated, 
(SELECT COUNT(*)
    FROM supplier_questions q2
    WHERE c.supplier_id = q2.supplier_id AND q2.reviewed = '1') AS reviewed, 
questapproved, 
ss.supplier_no AS supplier_no
FROM suppliers c
INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id
WHERE c.supplier_id != '0' AND ss.site_id = '2'
GROUP BY c.supplier_id
ORDER BY c.supplier_name ASC
LIMIT 0, 20
  

Результаты запроса Explain следующие

 id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY ss  ref site_id,supplier_id site_id 4   const   1287    Using where; Using temporary; Using filesort
1   PRIMARY c   eq_ref  PRIMARY PRIMARY 4   ss.supplier_id  1   
3   DEPENDENT SUBQUERY  q2  ref supplier_id,reviewed    reviewed    4   const   263 Using where
2   DEPENDENT SUBQUERY  q1  ref supplier_id,incomplete  incomplete  4   const   254 Using where
  

Причина, по которой есть запросы count, заключается в том, что мне нужно знать количество строк из этих таблиц, это невозможно сделать в другом запросе, поскольку результаты также должны быть отсортированы по этим значениям : (

Как удар в темноте, это выполняется быстрее? (У меня нет mysql для проверки синтаксиса, поэтому простите любые незначительные ошибки, но вы могли бы уловить идею)

 SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name, questions, reviewed 
IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated,  
questapproved,  ss.supplier_no AS supplier_no 
FROM suppliers c 
INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id 
inner join 
(SELECT supplier_id, sum(if(incomplete='0',1,0)) as questions,  sum(if(incomplete='1',1,0)) as reviewed FROM supplier_questions q1 group by supplier_id) as tmp
on c.supplier_id = tmp.supplier_id
WHERE c.supplier_id != '0' AND ss.site_id = '2' 
GROUP BY c.supplier_id 
ORDER BY c.supplier_name ASC LIMIT 0, 20 
  

 FROM suppliers c
INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id
WHERE c.supplier_id != '0' AND ss.site_id = '2'
GROUP BY c.supplier_id 
ORDER BY c.supplier_name ASC
  

Поскольку автоматически сгенерированные первичные ключи никогда не равны 0 (если не допущена ошибка при проектировании большой базы данных), вы можете удалить предложение c.supplier_id != ‘0’.

ss.site_id = ‘2’ должно быть в условии объединения для удобства чтения.

Похоже, что это должно соответствовать только одной строке в таблице supplier_site для каждого поставщика (если это ваше обычное отношение 1-N адресов объектов, т. Е. вы выбираете второй адрес каждого поставщика, возможно, ‘2’ соответствует ‘адресу выставления счетов’ или чему-то еще), поэтому ГРУППА ПО c.supplier_id бесполезна. Если GROUP BY действительно что-то делает, то запрос неверен, поскольку столбцы «address», которые предположительно поступают из таблицы supplier_site, будут получены из случайной строки.

Итак, вот упрощенный FROM (КУДА ушло) :

 FROM suppliers c
INNER JOIN supplier_site ss ON 
    (c.supplier_id = ss.supplier_id AND ss.site_id = '2')
ORDER BY c.supplier_name ASC
  

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

Теперь попробуйте этот запрос :

 SELECT a.*,
    questapproved, 
    ss.supplier_no AS supplier_no,
    IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated, 
    sum( q.incomplete = '0') AS questions,
    sum( q.reviewed = '1' ) AS reviewed
FROM
(
    SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name
    FROM suppliers c
    INNER JOIN supplier_site ss ON 
        (c.supplier_id = ss.supplier_id AND ss.site_id = '2')
    ORDER BY c.supplier_name ASC
    LIMIT 0, 20
) a
LEFT JOIN supplier_questions q ON (q.supplier_id = c.supplier_id)
GROUP BY c.supplier_id
ORDER BY c.supplier_name;
  

Если вы удалите вложенные запросы, вы получите что-то вроде этого:

 SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name,
COUNT(IF (q1.incomplete = '0', '0', null)) AS questions, 
IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated, 
COUNT(IF (q1.reviewed = '1', '1', null)) AS reviewed,
questapproved, 
ss.supplier_no AS supplier_no
FROM suppliers c
INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id
LEFT OUTER JOIN supplier_questions q1 ON c.supplier_id = q1.supplier_id
WHERE c.supplier_id != '0' AND ss.site_id = '2'
GROUP BY c.supplier_id
ORDER BY c.supplier_name ASC
LIMIT 0, 20
  

У меня нет доступной базы данных MySQL, поэтому в моем SQL могут быть ошибки.
Идея состоит в том, чтобы удалить вложенные запросы и заменить их внешним соединением
и используйте IF только для подсчета соответствующих строк.

Я бы сначала попытался реструктурировать, предварительно запросив агрегированные данные по количеству вопросов поставщика и проверив ИХ ОДИН РАЗ. Затем присоединитесь к остальным деталям. Используя ключевое слово STRAIGHT_JOIN, он должен обрабатываться в отображаемом порядке. Сначала это будет предварительная агрегация и использование этого в качестве основы для обратного соединения с поставщиками, а затем с сайтами поставщиков. Внешняя группа by не требуется, поскольку она все равно основана на идентификаторе поставщика. Однако присоединение к supplier_sites (ваш ss.supplier_no) будет означать, что у поставщика более одного местоположения. Означает ли это, что столбцы адреса и активного статуса происходят из этой таблицы?

Должно ли объединение вопросов быть связано с конкретным поставщиком и соответствующим местоположением сайта или нет?

Кроме того, поскольку предварительный запрос содержит предложение WHERE для supplier_id != ‘0’, в дальнейшем это не требуется, поскольку это будет основой обычного соединения с другими таблицами, что исключает их из результирующего набора.

 SELECT STRAIGHT_JOIN
      PreAggregate.supplier_id, 
      PreAggregate.supplier_name, 
      address1, 
      address2, 
      address3, 
      address4, 
      suppliertype, 
      postcode, 
      contact_name,
      PreAggregate.Questions,
      IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated, 
      PreAggregate.Reviewed,
      questapproved, 
      ss.supplier_no AS supplier_no
   FROM 
      (select 
             s1.Supplier_ID,
             s1.Supplier_Name,
             SUM( IF( q1.Incomplete = '0', 1, 0 )) Questions,
             SUM( IF( q1.Reviewed = '1', 1, 0 )) Reviewed
          from 
             suppliers s1
                join supplier_questions q1
                   ON s1.supplier_id = q1.supplier_id
          where
             s1.supplier_id != '0'
          group by
             s1.Supplier_ID 
          ORDER BY 
             s1.supplier_name ASC ) PreAggregate

       JOIN suppliers c
          ON PreAggregate.Supplier_ID = c.Supplier_ID

       JOIN supplier_site ss 
          ON PreAggregate.Supplier_ID = ss.supplier_id
          AND ss.Site_ID = '2'
  LIMIT 0, 20