Mutiple SQL count() from several tables

[eluser]agartzia[/eluser]
Hi! I'm trying to get some data from my database (MySQL) and I've been wandernig without any clear solution. Here's the problem:

I want to extract some data from my users (SELECT name, address, mail) and some statistics about them. I would like to get the count() on their Private Messages, Posts and Logged times.

I'm trying something like this

Code:

SELECT name,
       address,
       mail,
      (SELECT count(*) FROM pm WHERE user = "102") as total_pm,
      (SELECT count(*) FROM posts WHERE user = "102") AS total_posts,
      (SELECT count(*) FROM login WHERE user = "102") as total_login
FROM users
WHERE id = "102"
LIMIT 1

Of course it isn't working at all.

There's a way to make it without making 3-extra queries for each user?

Thanks!

[eluser]tonanbarbarian[/eluser]

Code:

SELECT users.name, user.address, users.mail,
COUNT(pm.*) AS total_pm, COUNT(posts.*) AS total_posts, COUNT(login.*) AS total_login
FROM users
INNER JOIN pm ON users.id=pm.user
INNER JOIN posts ON users.id=posts.user
INNER JOIN login ON users.id=login.user
WHERE id=102

[eluser]agartzia[/eluser]
[quote author="tonanbarbarian" date="1202483907"]

Code:

SELECT users.name, user.address, users.mail,
COUNT(pm.*) AS total_pm, COUNT(posts.*) AS total_posts, COUNT(login.*) AS total_login
FROM users
INNER JOIN pm ON users.id=pm.user
INNER JOIN posts ON users.id=posts.user
INNER JOIN login ON users.id=login.user
WHERE id=102

[/quote]

It worked! But it takes about 11 secs to make the query with just ONE inner join, which makes me think that I could hang the server if I try to run the THREE inners.

Could I find another way to count related results in other tables as a column in a query?

Thanks!

[eluser]tonanbarbarian[/eluser]
do you have indexes?
make sure that you have an index on the user fields in the pm, posts and login tables
and how many records per table are we talking about?
also you might want to change the COUNT(x.*) to COUNT(x.id) for each of the joined tables, that MIGHT improve performance as well

[eluser]tonanbarbarian[/eluser]
actually the reason it takes 11 seconds is because you have queries in the select fields
this proper way of using the joins should be vastly quicker than what you originally tried
just make sure you have the correct indexes set up and it should be fine

[eluser]agartzia[/eluser]
Yes, I got primary key in every table. And yes, I've tried the query using the count(id) instead of using the count(table.*).

I've limited the query to the users table to 20 results. In the other tables, we're talking about 3000-5000 records per table.

Finally, I've crashed the server!

[eluser]tonanbarbarian[/eluser]
you need indexes not just primary keys
indexes help to lookup the data quickly
for example when you are joining users to pm
you users table has the primary key which is good
but if the pm table does not have an index (or key) on the user field then it will slow down the join between user and pm

[eluser]agartzia[/eluser]
OK. I've reading some about indexes (not primary keys, which I thought it was the same thing), but it's somehow messy! Anyway I'll keep on reading!

Cheers, man!

[eluser]oliviermarian[/eluser]
hello,
is there a proper way to code this using active record CI code, or is it better to use the sql query syntax ?

I mean:

Code:

$sql="SELECT users.name, user.address, users.mail,
COUNT(pm.*) AS total_pm, COUNT(posts.*) AS total_posts, COUNT(login.*) AS total_login
FROM users
INNER JOIN pm ON users.id=pm.user
INNER JOIN posts ON users.id=posts.user
INNER JOIN login ON users.id=login.user
WHERE id=102";
$data['query'] = $this->db->query($sql);

or something like

Code:

this->db->select('users.name, user.address, users.mail');
$this->db->from('users');
$this->db->join("pm", "users.id=posts.user");
$this->db->join("posts", "users.id=posts.user");
$this->db->join("login", "users.id=login.user");
$this->db->stop_cache();
$total = $this->db->count_all_results();
$query = $this->db->get();
$this->db->flush_cache();
return $query->result();

1 - I don't know if it works
2 - Is it worth trying once we have the sql query working ?

[eluser]johnwbaxter[/eluser]
Enable the profiler. When you run the page with the active record query on it will print out the actual sql generated so you can see what the sql looks like.