Ed wrote:
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.
What indexes do you have?
Do you have one on
taskinput(user_id)
and
users(user_id) ?
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
