[MySQL] Help Optimize queries to select data from 2 tables

[mtha]

I want to do the following two thing:


1- Select all posts (with its information) belong to one thread
something like:


$threadid == X;

$posts = $DB_site->query("
SELECT post.*, post.userid FROM post WHERE (post.threadid = $threadid)
");

$post = $DB_site->fetch_array($posts)


giving me a list of posts, says 15 posts (15 rows)


2. For each posts (each row), there is one userid, I need to select all awards that belong to that userid
something like


$alluserawards = $DB_site->query("
SELECT award.* FROM award WHERE userid=$post[userid]
");


giving me a list of awards, says 5 awards (5 rows)

if I run query #2 for each of the rows, it will run 15 times, + 1 query for #1,

is there any way to optimize the queries? so that I can somehow get a list of awards for each userid.

[Dean C]

Untested:


SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid


You didn't give any info on your db schema for the awards table so I'm afraid I can't be of much help other than the query above which may or may not work depending what you have in the awards table :)

[mtha]

Untested:


SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid


You didn't give any info on your db schema for the awards table so I'm afraid I can't be of much help other than the query above which may or may not work depending what you have in the awards table :)

yeah, my mind was just out :D the query is kind of simple. Thanks Dean C
The db schema is available in my new hack
https://vborg.vbsupport.ru/showthread.php?t=78934


$userawards = $DB_site->query("
SELECT a.*, au.*, post.userid, post.postid
FROM " . TABLE_PREFIX . "post, " . TABLE_PREFIX . "award_user au, " . TABLE_PREFIX . "award a
WHERE $postids AND au.userid=post.userid AND a.award_id=au.award_id
GROUP BY au.issue_id
");

[Marco van Herwaarden]

You really should use the LEFT JOIN like Dean showed you.

Also you will need to add an AS to the FROM clauses:
" . TABLE_PREFIX . "post AS post
Or things will go wrong if a table prefix is used.

[Xenon]

Dean's left join won't work, as it would just select ONE award per post.

you have two possibilities:
1) put both tables into the from tag
2) cache the results of query two with one big query, and then work with the cache

[mtha]

You really should use the LEFT JOIN like Dean showed you.

Also you will need to add an AS to the FROM clauses:
" . TABLE_PREFIX . "post AS post
Or things will go wrong if a table prefix is used.
Got it work perfectly here
https://vborg.vbsupport.ru/showthread.php?t=78934

Thank you very much :)

[mtha]

Untested:


SELECT post.*
awards.*
FROM " . TABLE_PREFIX . "post as post
LEFT JOIN
post
ON
post.userid = award.userid



Could you tell me the different between ON and USING on JOIN command?

says

LEFT JOIN post ON (userid)
and
LEFT JOIN post USING (userid)

[Xenon]

using just allows a column-name, ON instead allows more complex conditions like for example:

SELECT * FROM post LEFT JOIN user ON (user.userid = post.userid * 2)
or whatever (not that the statement above would be usefull ;))