CODEWITHSUNDEEP
phpmysqlsqldatabase
user2533395
user2533395

Reputation: 33

Finding username of the last post in mysql table joining 3 tables

I know I am doing this qry wrong - its taking over 10secs for the results to come back w/ only 3000 rows of data... I have 3 tables:
users
id
username

DATA:
1|tom
2|dick
3|harry



posts
id
id_users

DATA:
1|1
2|1
3|1
4|2
5|2
6|3

cronjobs
id
id_post
id_wall

DATA:   id|id_post|id_wall
1|1|1
2|1|2
3|1|3
4|1|4
5|1|5
6|2|5
7|4|3
8|6|3
9|4|4

A user will make a post That post will be put on one or more walls and stored in the cronjobs table. The id on cronjobs is auto increment. I need to get the username and last post on each wall. In the above example

Dick was the last person to post on wall 4 with post 4
Harry was the last person to post on wall 3 with post 6
Tom was the last person to post on wall 5 with post 2
Tom was the last person to post on wall 2 with post 1
Tom was the last person to post on wall 1 with post 1

Here is the qry im currently using, I know using the IN clause with the select inside it, is killing this....

SELECT 
  c.id,
  c.id_post,
  c.id_wall AS id_wall,
  p.id_users AS user_id,
  u.NAME AS username 
FROM
  cronjobs c,
  posts p,
  users u 
WHERE c.id IN 
  (SELECT MAX(id) 
  FROM
    cronjobs 
  GROUP BY id_wall) 
  AND c.id_post = p.id 
  AND p.id_users = u.id 
ORDER BY c.id

Any help is appreciated!

Upvotes: 1

Views: 53

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use join instead of a subquery in where clause, subquery in join part will be executed once while subquery in where clause will be executed for all the resultant rows this query will perform much better than yours, also index your columns that appears in on() part

SELECT 
  c.id,
  c.id_post,
  c.id_wall AS id_wall,
  p.id_users AS user_id,
  u.username AS username 
FROM
  cronjobs c
  join (SELECT  MAX(id) id ,id_wall
      FROM cronjobs 
      GROUP BY id_wall) c1 on c.id = c1.id and c.id_wall = c1.id_wall
  join posts p on c.id_post = p.id 
  join users u on p.id_users = u.id 
ORDER BY c.id

DEMO

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

SELECT 
  c.id,
  c.id_post,
  c.id_wall AS id_wall,
  p.id_users AS user_id,
  u.NAME AS username 
FROM
  cronjobs c,
  posts p,
  users u ,
  (SELECT MAX(id) as id 
  FROM
    cronjobs 
  GROUP BY id_wall) table_id
  where c.id_post = p.id 
  AND p.id_users = u.id 
and table_id.id  =c.id
ORDER BY c.id

Upvotes: 1

Related Questions