|
| Hi,
I've been expermenting with displaying stats for our intranet and I'm
looking at making it more robust so it can display stats better.
I've come up with the most ugly! long winded SQL statement You could
imagine.
SELECT DISTINCT u.*, t.*, (SELECT COUNT(jobtype) FROM taskinput WHERE
t.user_id = u.user_id ) AS 'COUNT', (SELECT COUNT(jobtype) FROM taskinput
WHERE t.user_id = u.user_id AND t.jobtype = 'Navision') AS 'Navision',
(SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND
t.jobtype = 'Abuse'') AS 'Abuse', (SELECT COUNT(jobtype) FROM taskinput
WHERE t.user_id = u.user_id AND t.jobtype = 'Tickets') AS 'Tickets', (SELECT
COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id) AS 'MegaTotals'
FROM users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id,
t.user_id ORDER BY COUNT DESC
I've never down anything so long in all the time i've been playing and it's
causing my CPU to go crazy although i'm only using 126mb of my 2.5GB of RAM.
The query in question is fine if I strip out the WHERE t.user_id = u.user_id
from each SELECT COUNT it goes through in less than a second in fact but
doesnt display the results properly it gives every user the same listings.
Surely there must be a more efficent way of doing this? but i've not been
able to find one that executes quickly.
Any help of stripping the code down too it's bare minimum and getting the
results quickly would be welcome :)
Thanks
Ed
|
|