0

I was looking for a solution to search friends by an exact name or a prefix of their first or last name. my requirement is something similar to this link. Sphinx vs. MySql - Search through list of friends (efficiency/speed) but the post does not explain the how to do it approach. It does say that searching across relationship through sphinx is not recommended. Can anyone please suggest whether i should use any of the following approaches or an entirely different approach. Also i am looking for high speed and scalability for say more than a million records

DB structure is something similar.

InnoDB

table1- users: uid, fname, lname, city, country, picurl, status (picurl & status may change anytime and sphinx is not realtime & just say or imagine that even 10 minutes lag is not acceptable in my design for these two fields.)

table2- friends: uid,fid

goal is to search: all friends of "uid 11" having prefix 'tony' in their fname or lname or full name and pull out their details.

  • Approach 1:

Sphinx: search on uid, fname, lname (attributes: city, country)

a) Do a mysql search to pull out the friend ids of "uid 11" & store in array X

b) then do sphinx search for 'tony*' filtered docid by the array X.

c) then again do a mysql search to pull the realtime fields like picurl,status for each of the matching friendids.

OR

  • Approach 2:

Sphinx: search on uid, fname, lname (attributes: city, country) + MVA: friendids (MVA to store all friend ids)

a)search sphinx for 'tony*' filtered by MVA that contains uid '11'

b)then again do a mysql search to pull the realtime fields like picurl,status for each of the matching friendids.

  • Approach 3:

No Sphinx

a)Mysql search friends of "uid 11" where fname like 'tony%' OR lname like 'tony%' OR CONCAT(fname, ' ', lname) LIKE 'tony stark%' OR CONCAT(lname, ' ', fname) LIKE 'tony stark%'and pullout the details in the same query join (From the other posts this seems to be low on performance for large number of records)

  • Approach 4:

    Any suggestions?

PS: my next challenge is to search for friends of friends and so the question header..

Community
  • 1
  • 1
DevD
  • 1,201
  • 2
  • 12
  • 20

1 Answers1

0

Would recommend Approach 2

But for Approach 4 - would porbably be to use a dedicated Graph database. (or a dedicated engine for mysql) - for just prefix searches, you dont strictly need something like sphinx.

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Thanks for giving me a direction to work on. So Approach4 should be better than Approach2 rite? Also would it be better to use neo4j along with mysql or use a mysql engine like oqgraph that you have mentioned(is it the best mysql engine?). (i ask this because i read that neo4j is persistent and oqgraph not.) Also which of these would be more efficient for a large database. Also can you please provide some good links for understanding when not to use RDBMS cause there seem to be a sea of no-sql options out there. After reading all these it seems mysql really is not that great with large DBs. – DevD May 25 '13 at 11:45
  • I mean RDBMS dont seem to be good with large Data. did not mean to pin point mysql as such. – DevD May 25 '13 at 11:55