Slow quiry?

[NogDog]

The SQL error was a typo by me: I meant to use AS ff as the alias for the friend_friend table.

[jasondavis]

OK I have tried both versions now and the speed seems to be around the same for both of these, both give me an error when I try to use submit_date from friend_bulletin though

SELECT auto_id, user_id, bulletin, subject, color, spam
  FROM friend_bulletin, friend_friend
  WHERE friend_friend.status = 1 
  AND friend_friend.friendid = 1
  AND friend_bulletin.user_id = friend_friend.userid
  Order by friend_bulletin.auto_id DESC
  LIMIT 0 , 20

AND

SELECT auto_id, user_id, bulletin, subject, color
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE ff.status =1
AND ff.friendid =1
ORDER BY fb.auto_id DESC
LIMIT 0 , 20 

[jasondavis]

if($_SESSION['spam']=='1'){
	$spam=" AND fb.spam =1";
} else {
	$spam='';
}



SELECT auto_id, user_id, bulletin, subject, color, submit_date
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE ff.status =1
AND ff.friendid =1
$spam //this part is not working
ORDER BY fb.submit_date DESC
LIMIT 0 , 20 

Im having 2 problems

1) #1052 - Column 'submit_date' in field list is ambiguous

2) $spam the value of this does not change teh results I receive from the DB

[NogDog]

SELECT auto_id, user_id, bulletin, subject, color, [color=red]fb.[/color]submit_date

[veronicabend]

Have you tried using "explain" before any of these queries to see how mysql will work with them? Just put the word explain at the beginning of the query and check the result. This will tell you how many rows mysql will have to evaluate, which indexes it will use, if it will use filesort or temporary. You can look in the mysql manual for help on how to analyze the explain results.

http://dev.mysql.com/doc/refman/4.1/en/explain.html

Veronica

[jasondavis]

Almost working 1 small problem now, AND fb.spam =0
This returns 0 results but if I do spam=1 it returns all results marked with 1 under spam

SELECT auto_id, user_id, bulletin, subject, spam, color, fb.submit_date
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE ff.status =1
AND ff.friendid =1
AND fb.spam =0
ORDER BY fb.submit_date DESC
LIMIT 0 , 20 

[jasondavis]

Ok almost got it all working, 1 last thing though

I need something like OR fb.user_id=1 to work so if I posted a buleltin myself it will still show up in my list but it isnt working, it will select all results up until it gets to my own bulletin, then it will show my same bulletin for like 8,000 times after

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE ff.status =1
AND ff.friendid =13
AND fb.spam='1'
OR fb.user_id=13 //This line isnt working correctly
ORDER BY fb.submit_date DESC
LIMIT 0 , 20

[bpat1434]

You need to group things together using ( and ) so that the OR isn't an "OR" of the last AND.

WHERE (ff.status =1
AND ff.friendid =13
AND fb.spam='1')
OR fb.user_id=13

[jasondavis]

Ok so new code

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE (
ff.status =1
AND ff.friendid =13
AND fb.spam = '1'
)
OR fb.user_id =13
ORDER BY fb.submit_date DESC
LIMIT 0 , 20 

the result seems to still return a bunch of buleltins posted from friend ID 13, they are the same buleltin over and over though, please see attached image

[jasondavis]

when I use SELECT DISTINCT it corrects this problem but take like 2 seconds to load instead of .04

[jasondavis]

here is my newest code, it is pretty fast but I have to run this 2 times, 1 to get results and 1 to get row count, is there a faster way to get the row count?

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
AND spam = '1'
ORDER BY submit_date DESC
LIMIT 0 , 20


select count(*)
FROM friend_bulletin AS fb
WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
AND spam = '1'

[bpat1434]

Why not just use the [man]mysql_num_rows/man function on the result?

[jasondavis]

bpat1434 wrote:

Why not just use the [man]mysql_num_rows/man function on the result?

I just tried this, the problem with it is it returns only the number I am showing, I need the count to do my paging and show total results so since I limit to 20 per page it only returns 20 as the count, any other ideas?

[jasondavis]

Ok it seems this quiry only works good on limit 0,20 when I get into limit 5600,5620 for example or any high number it starts to take 4-5 seconds, and the join statement is fast but I can't get the join statement to work wiht showing my own bulletins

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
AND spam = '1'
ORDER BY submit_date DESC
LIMIT 0 , 20 

here is the Join one which won't show my own results

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE (
ff.status =1
AND ff.friendid =13
AND fb.spam = '1'
)
OR fb.user_id =13
ORDER BY fb.submit_date DESC
LIMIT 0 , 20