Блог

У меня есть две таблицы. uploads и profits .

загружает:

 ╔════╦══════════════╦══════════════════╗
║ id ║  name        ║ more columns...  ║
╠════╬══════════════╬══════════════════╣
║  1 ║ Jeff Atwood  ║ ................ ║
║  2 ║ Geoff Dalgas ║ ................ ║
║  3 ║ Jarrod Dixon ║ ................ ║
║  4 ║ Joel Spolsky ║ ................ ║
╚════╩══════════════╩══════════════════╝
  

прибыль:

 ╔══════════╦══════════════╦══════════════════╗
║ uploadid ║    amount    ║ more columns...  ║
╠══════════╬══════════════╬══════════════════╣
║    1     ║     4.0      ║ ................ ║
║    1     ║     7.2      ║ ................ ║
║    3     ║     6.3      ║ ................ ║
║    4     ║     2.5      ║ ................ ║
╚══════════╩══════════════╩══════════════════╝
  

Как вы можете видеть, uploads . id => profits . uploadid

Я хочу отобразить несколько строк из uploads таблицы с еще одним столбцом, который сообщает мне, сколько «прибыли» существует.

Пример для результата:

 ╔════╦══════════════╦════════════════╦══════════════════╗
║ id ║  name        ║  profitsCount  ║ more columns...  ║
╠════╬══════════════╬════════════════╬══════════════════╣
║  1 ║ Jeff Atwood  ║       2        ║ ................ ║
║  2 ║ Geoff Dalgas ║       0        ║ ................ ║
║  3 ║ Jarrod Dixon ║       1        ║ ................ ║
║  4 ║ Joel Spolsky ║       1        ║ ................ ║
╚════╩══════════════╩════════════════╩══════════════════╝
  

Примечание: в реальной таблице uploads . id и profits . uploadid столбцы есть varchar и нет int , я сделал это здесь, int чтобы быть более понятным.

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

Мой запрос:

 SELECT          `uploads`.* ,COUNT(`profits`.`uploadid`) AS `numProfits`
FROM            `uploads`
LEFT JOIN       `profits`
ON              `uploads`.`id` = `profits`.`uploadid`
GROUP BY        `uploads`.`id`
ORDER BY        `numProfits`
DESC
LIMIT           30
  

Это ваш запрос:

 SELECT u.* ,COUNT(p.uploadid) AS numProfits
FROM uploads LEFT JOIN
     profits p
     ON u.id = p.uploadid
GROUP BY u.id
ORDER BY numProfits DESC
LIMIT 30;
  

Первое улучшение: создайте индекс на profits(uploadid) . Это, вероятно, решит вашу проблему. Возможно, вы сможете добиться еще большей производительности с:

 select u.*,
       (select count(*) from profits p where u.id = p.uploadid) as numProfits
from uploads u
order by numProfits desc
limit 30;
  

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

Вы также можете попробовать агрегирование в подзапросе:

 select u.*, numProfits
from uploads u join
     (select uploadid, count(*) as numProfits
      from profits p
      group by uploadid
      order by numProfits desc
      limit 30
     ) p
     on u.id = p.uploadid;
order by numProfits desc;
  

Редактировать:

Для последнего решения, чтобы выбирать строки, даже если они не приносят прибыли, используйте left join и coalesce() :

 select u.*, coalesce(numProfits, 0) as numProfits
from uploads u left join
     (select uploadid, count(*) as numProfits
      from profits p
      group by uploadid
      order by numProfits desc
      limit 30
     ) p
     on u.id = p.uploadid;
order by numProfits desc;
  

Вы можете минимизировать время запроса SQL select, индексируя поле order by, однако это может (и будет) повлиять на ваше время вставки (и обновляться всякий раз, когда затрагивается столбец индекса)