mysql - SQL query runs very slow using order by -

mysql - SQL query runs very slow using order by -

i have 2 tables. uploads , profits.

uploads:

╔════╦══════════════╦══════════════════╗ ║ id ║ name ║ more columns... ║ ╠════╬══════════════╬══════════════════╣ ║ 1 ║ jeff atwood ║ ................ ║ ║ 2 ║ geoff dalgas ║ ................ ║ ║ 3 ║ jarrod dixon ║ ................ ║ ║ 4 ║ joel spolsky ║ ................ ║ ╚════╩══════════════╩══════════════════╝

profits:

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

as can see, uploads.id => profits.uploadid

i want display rows uploads table 1 more column tells me how many "profits" there are.

example result:

╔════╦══════════════╦════════════════╦══════════════════╗ ║ id ║ name ║ profitscount ║ more columns... ║ ╠════╬══════════════╬════════════════╬══════════════════╣ ║ 1 ║ jeff atwood ║ 2 ║ ................ ║ ║ 2 ║ geoff dalgas ║ 0 ║ ................ ║ ║ 3 ║ jarrod dixon ║ 1 ║ ................ ║ ║ 4 ║ joel spolsky ║ 1 ║ ................ ║ ╚════╩══════════════╩════════════════╩══════════════════╝

note: in real table uploads.id , profits.uploadid columns varchar , not int, did here int more clear.

the problem when run query big tables (thousands of rows) takes lots of time

my query:

select `uploads`.* ,count(`profits`.`uploadid`) `numprofits` `uploads` left bring together `profits` on `uploads`.`id` = `profits`.`uploadid` grouping `uploads`.`id` order `numprofits` desc limit 30

this query:

select u.* ,count(p.uploadid) numprofits uploads left bring together profits p on u.id = p.uploadid grouping u.id order numprofits desc limit 30;

first improvement: create index on profits(uploadid). solve problem. might able improve performance with:

select u.*, (select count(*) profits p u.id = p.uploadid) numprofits uploads u order numprofits desc limit 30;

this eliminates need file sort aggregation. prefer first version explicit aggregation, subquery can work better.

you can seek aggregation in subquery:

select u.*, numprofits uploads u bring together (select uploadid, count(*) numprofits profits p grouping uploadid order numprofits desc limit 30 ) p on u.id = p.uploadid; order numprofits desc;

edit:

for lastly solution, take rows don't have profit, utilize left join , coalesce():

select u.*, coalesce(numprofits, 0) numprofits uploads u left bring together (select uploadid, count(*) numprofits profits p grouping uploadid order numprofits desc limit 30 ) p on u.id = p.uploadid; order numprofits desc;

mysql sql performance select sql-order-by