Performance of nested select from multiple tables [closed]

i have query with multiple nested selects from multiple tables as follows:

select c.name,c.created_date,
(  select count(id) from user u 
   where u.company_id=c.id ) as users,
(  select count(distinct uls.user_id) from user_login_status uls 
   where uls.company_id=c.id 
     and uls.date between '2013-11-18' and '2014-01-10' ) as unique_logins,
(  select count(id) from cash_receiving cr 
   where cr.company_id=c.id 
   and cr.created_date between '2013-11-18' and '2014-01-10' ) as recognitions,
0 as licences,
(  select count(id) from inventory_item i 
   where i.company_gid=c.id ) as store_items,
(  select count(id) from award a 
   where a.company_gid=c.id ) as awards,
(  select uls2.date from user_login_status uls2 
   where uls2.company_id=c.id 
   ORDER BY uls2.date DESC LIMIT 1 ) as last_login,
 c.id as id,
 c.is_live as live 
 from company c 
 order by c.name

the user_login_status contains entry for each user login, so it will have massive data, that is the sql side, as for the java side i am invoking above query with entity manager so i was wondering about performance of above query, will it act well with massive data for each table, or it’s better to make separate select statements, please advise how to make the above query perform well, thanks.

Answer

Try this:

SELECT c.name,c.created_date, u.users, uls.unique_logins, cr.recognitions, 
       0 AS licences, i.store_items, s.awards, uls2.last_login, 
       c.id AS id, c.is_live AS live 
FROM company c 
LEFT JOIN (SELECT u.company_id, COUNT(u.id) users 
           FROM user u 
           GROUP BY u.company_id
         ) AS u ON u.company_id = c.id
LEFT JOIN (SELECT uls.company_id, COUNT(DISTINCT uls.user_id) unique_logins 
           FROM user_login_status uls WHERE uls.date BETWEEN '2013-11-18' AND '2014-01-10' 
           GROUP BY uls.company_id
         ) AS uls ON uls.company_id=c.id 
LEFT JOIN (SELECT cr.company_id, COUNT(cr.id) recognitions FROM cash_receiving cr 
           WHERE cr.created_date BETWEEN '2013-11-18' AND '2014-01-10'
           GROUP BY cr.company_id
         ) AS cr ON cr.company_id = c.id 
LEFT JOIN (SELECT i.company_gid, COUNT(i.id) store_items 
           FROM inventory_item i 
           GROUP BY i.company_gid
         ) AS  i ON i.company_gid = c.id
LEFT JOIN (SELECT a.company_gid, COUNT(a.id) awards 
           FROM award a 
           GROUP BY a.company_gid
         ) AS  a ON a.company_gid = c.id
LEFT JOIN (SELECT uls2.company_id, MAX(uls2.date) last_login 
           FROM user_login_status uls2 
           GROUP BY uls2.company_id
         ) AS uls2 ON uls2.company_id=c.id 
ORDER BY c.name

Leave a Reply

Your email address will not be published. Required fields are marked *