Sphinx vs. MySql - Search through list of friends (efficiency/speed)

Viewed 2408

I'm porting my application searches over to Sphinx from MySQL and am having a hard time figuring this one out, or if it even needs to be ported at all (I really want to know if it's worth using sphinx for this specific case for efficiency/speed):

users
uid uname
  1    alex
  2    barry
  3    david

friends
uid | fid
  1     2
  2     1
  1     3
  3     1

Details are:
- InnoDB
- users: index on uid, index on uname
- friends: combined index on uid,fid

Normally, to search all of alex's friends with mysql:

$uid = 1
$searchstr = "%$friendSearch%";
$query = "SELECT f.fid, u.uname FROM friends f 
          JOIN users u ON f.fid=u.uid
          WHERE f.uid=:uid AND u.uname LIKE :friendSearch";
$friends = $dbh->prepare($query);
$friends->bindParam(':uid', $uid, PDO::PARAM_INT);
$friends->bindParam(':friendSearch', $searchstr, PDO::PARAM_STR);
$friends->execute();

Is it any more efficient to find alex's friends with sphinx vs mysql or would that be an overkill?
If sphinx would be faster for this as the list hits thousands of people, what would the indexing query look like? How would I delete a friendship that no longer exists with sphinx as well, can I have a detailed example in this case? Should I change this query to use Sphinx?

4 Answers
Related