Performance of nested select from multiple tables [closed]

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

(  select count(id) from user u 
   where ) as users,
(  select count(distinct uls.user_id) from user_login_status uls 
     and between '2013-11-18' and '2014-01-10' ) as unique_logins,
(  select count(id) from cash_receiving cr 
   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 ) as store_items,
(  select count(id) from award a 
   where ) as awards,
(  select from user_login_status uls2 
   ORDER BY DESC LIMIT 1 ) as last_login, as id,
 c.is_live as live 
 from company c 
 order by

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.


Try this:

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

Leave a Reply

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