CodingForums.com - Fast select count

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Fast select count (http://www.codingforums.com/showthread.php?t=282237)

Fast select count

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 :)

Quote:

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.

Code:

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:

Code:

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.

Quote:

Originally Posted by Fumigator (Post 1292275)

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

Code:

SELECT s1.nick

FROM statuses as s1

JOIN statuses as s2

ON s1.id = s2.shared_from_id

WHERE s2.shared_from_id <> 0

Code:

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

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