Slow quiry?

jasondavis

Ok here is my slow part of code

It gathers the friend ID of all the people I am friends with on my site, the more id's the slower the code gets, I'm at around 20,000 friends and the result takes a while now and I am worried as every user on my site has to deal with the lagging code, is there another way to accomplish this?

function findfriend() {
	$temp = $_SESSION['info'];
	$sql_friend = "select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'";
	$result_friend = executeQuery($sql_friend);
	while ($line_friend = mysql_fetch_array($result_friend)) {
		$friend[] = $line_friend['friendid'];
	}
	$cnt = count($friend);
	if ($cnt > 0) {
		$str_friends_ids = implode(",", $friend);
	} else {
		$str_friends_ids = '';
	}
	return $str_friends_ids;
}


$str_friend_ids=findfriend();  // gathers the friend ID's of the users buleltins I am allowed to view


SELECT auto_id, user_id, bulletin, subject, color, submit_date
FROM friend_bulletin
WHERE 1=1
AND (
user_id = '1'
OR user_id
IN (
$str_friend_ids
)
ORDER BY submit_date DESC
LIMIT 0 , 20

Also Yes I do have indexes on my friend_friend table

bpat1434

One thing that stands out is that you're pulling 3 columns of info, but only utilizing one. You can remove userid and status as returned columns.

You could compact your code to be:

return((sizeof($friend) > 0) ? implode(',', $friend) : '');

But that probably won't speed up the code.

In your second query (which I'm guessing you run later but wanted to show) you're missing a closing ")" and you can probably do away with the 1=1 part and just do

WHERE user_id IN (' . $str_friend_ids . ')
    OR user_id=1

Also note that integers (like IDs) don't need quotes around them. So your original query can be:

    $sql_friend = "SELECT friendid 
FROM friend_friend 
WHERE userid=" . (int)$temp[auto_id] ."
   AND status=1";

Not sure how quickly that will speed things up. But it should help.

jasondavis

thanks for the tips, Any other advice anyone, this problem will only get worse I beleive as my site gets bigger with people having more friends and more bulletins being posted

NogDog

Have you assigned indexes for those columns used in where and order by clauses?

jasondavis

Hi yes I have asigned indexes, the problem is I think is for example lets say there is 20,000 members and I am a friend with 17,000 of them, it will return a string of 17,000 friend ID's and see if any of them have bulletins posted, so as my userbase/friendbase gets larger it gets slower but I need to figure out how to improve for when the site is much larger, many sites have figured out how to deal with this but I don't see how

NogDog

You should be able to do it in just one query using a join, something like:

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

This is just an educated guess, as I'm not sure of the exact relationships between your tables.

jasondavis

interesting aproach im gonna give this a try tomorrow

Only thing I dont see is where to input my friendID which is called on the page by $info[auto_id]

jasondavis

Here is what I tried

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

the result is

#1066 - Not unique table/alias: 'fb'

here are the 2 table structures if this helps anything, appreciate the help

-- 
-- Table structure for table `friend_bulletin`
-- 

CREATE TABLE IF NOT EXISTS `friend_bulletin` (
  `auto_id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `bulletin` text NOT NULL,
  `subject` varchar(255) NOT NULL default '',
  `color` varchar(6) NOT NULL default '000000',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` enum('Active','In Active') NOT NULL default 'Active',
  `spam` enum('0','1') NOT NULL default '1',
  PRIMARY KEY  (`auto_id`),
  KEY `user_id` (`user_id`),
  KEY `submit_date` (`submit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=34860 ;



-- 
-- Table structure for table `friend_friend`
-- 

CREATE TABLE IF NOT EXISTS `friend_friend` (
  `autoid` int(11) NOT NULL auto_increment,
  `userid` int(10) default NULL,
  `friendid` int(10) default NULL,
  `status` enum('1','0','3') NOT NULL default '0',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=534307 ;

jasondavis

I just tried something similar

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

This code works and cuts the page generation time almost in half but would a JOIN be even faster?

Also when I add in, submit_date I get this error
#1052 - Column 'submit_date' in field list is ambiguous

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'