Fast select count

[Trki]

Hi guys .. i have a trouble i want to select count(id) FROM 5 tables and choose the quicker way...

I tried something like


SELECT
(SELECT (COUNT(id)) AS likes FROM status_likes WHERE nick='Trki') as likes_count,
(SELECT (COUNT(id)) AS comments FROM comments WHERE nick='Trki') as comments_count,
(SELECT (COUNT(id)) AS statuses FROM statuses WHERE nick='Trki') as statuses_count


which works good but isn't it as slow as i would make 5mysql queries separetly with SELECT ?

OR is there some better way? :P

THX!


+++ ANOTHER PROBLEM

This query SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0 returns this:

shared_from_id
3481
3455
3423
3388
3386
3362
3272
3117

What is good.. and why then i cant use the all query like this ? :

SELECT nick FROM statuses WHERE id IN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0)

The program died or show the result with duration more then 31s :/


IF someone would help i would be glad

[Fumigator]

What is good.. and why then i cant use the all query like this ? :

SELECT nick FROM statuses WHERE id IN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0)

The program died or show the result with duration more then 31s :/

Using "WHERE id IN" in this way is going to kill you. Just join the table with itself.

SELECT s1.nick
FROM statuses as s1
JOIN statuses as s2
ON s1.id = s2.shared_from_id
WHERE s2.shared_from_id <> 0

This can give you multiple rows for every ID though... I guess you don't want that. Should be simple enough to handle with code after the query runs. If that's no good then replace the 2nd table reference with a temp table using your SELECT DISTINCT query like this:

SELECT s1.nick
FROM statuses as s1
JOIN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0) as s2
ON s1.id = s2.shared_from_id
WHERE s2.shared_from_id <> 0

Not sure what performance will be like on that query though.

Might be a good idea to add an index on shared_from_id.

p.s. You don't even need the "WHERE" clause, unless you want to exclude the row where id = 0. But it shouldn't matter too much.

[Trki]

Originally Posted by Fumigator

Using "WHERE id IN" in this way is going to kill you. Just join the table with itself.

SELECT s1.nick
FROM statuses as s1
JOIN statuses as s2
ON s1.id = s2.shared_from_id
WHERE s2.shared_from_id <> 0

This can give you multiple rows for every ID though... I guess you don't want that. Should be simple enough to handle with code after the query runs. If that's no good then replace the 2nd table reference with a temp table using your SELECT DISTINCT query like this:

SELECT s1.nick
FROM statuses as s1
JOIN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0) as s2
ON s1.id = s2.shared_from_id
WHERE s2.shared_from_id <> 0

Not sure what performance will be like on that query though.

Might be a good idea to add an index on shared_from_id.

p.s. You don't even need the "WHERE" clause, unless you want to exclude the row where id = 0. But it shouldn't matter too much.

HM.. your code works gr8 really nice :P But i dont need just this: (from your code which are the authors of statuses ... )

nick;id
Paulis1992;3362
george12;3272
jdawg91;3388
Horin;3386
Horin;3386
Horin;3386
Horin;3386
Horin;3386
Horin;3386
Horin;3386
domin47;3423
masuronike;3117
masuronike;3481
george12;3455

but also this:

nick;shared_from_id
george12;3362
Gianniluke;3272
rdy2win;3388
martinaltman;3386
Trki;3386
masuronike;3386
C1zk0;3386
pLAYERsvk;3386
JoHnnn;3386
MACHi;3386
Out92;3423
czechpete;3117
Strupik;3481
Strupik;3455

which is SELECT nick,shared_from_id FROM statuses WHERE shared_from_id<>0

... the people who shared...ideas ? :P