----- Original Message -----
From: "Chris" <dmagick@xxxxxxxxx>
To: "Ed" <ed@xxxxxxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Friday, January 05, 2007 5:23 AM
Subject: Re: Multiple Count's in one Select.
[ please don't top-post, makes it hard to follow ]
Ed wrote:
As this is a new db i've not done any indexing.
There's only 4 rows in the table.
SELECT DISTINCT u.user_id, u.username, t.user_id, t.jobtype, (SELECT
COUNT(jobtype) FROM taskinput) AS 'COUNT', (SELECT COUNT(jobtype) FROM
taskinput WHERE t.jobtype = 'Navision') AS 'Navision', (SELECT
COUNT(jobtype) FROM taskinput WHERE t.jobtype = 'Abuse') AS 'Abuse',
(SELECT COUNT(jobtype) FROM taskinput WHERE t.jobtype = 'Tickets') AS
'Tickets', (SELECT COUNT(jobtype) FROM taskinput) AS 'MegaTotals' FROM
users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id,
t.user_id;
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
| user_id | username | user_id | jobtype | COUNT | Navision | Abuse
| Tickets | MegaTotals |
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
| 1 | edd | 1 | Tickets | 4 | 0 |
0 | 0 | 4 |
| 10 | administrator | 10 | Tickets | 4 | 0 | 0
| 4 | 4 |
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
2 rows in set (0.00 sec)
Notice how i've removed the WHERE t.user_id = u.user_id from each SELECT
count? it's flown through but seems to be putting all the same results in
each count.
It takes far too long to get any kind of result where adding the extra
WHERE t.user_id = u.user_id AND t.jobtype = 'Tickets'
It's got me stumped, but then i've not slept in nearly 24 hours.
Can you provide table info or a small database dump ? Probably best to
send off list if that's ok.
You have a complicated query so it'll be easier seeing the table
definitions and having some data to work with ;)
----- Original Message ----- From: "Chris" <dmagick@xxxxxxxxx>
To: "Ed" <ed@xxxxxxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Friday, January 05, 2007 5:03 AM
Subject: Re: Multiple Count's in one Select.
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/
--
Postgresql & php tutorials
http://www.designmagick.com/
Sent a DB dump to you off list :)
Thanks again.
Ed
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
