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
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.
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