CODEWITHSUNDEEP
phpmysqlsql
Craig White
Craig White

Reputation: 14002

MySQL Search for line in text

in MySQL, I have a row for each user, with a column that contains friends names separated by \n.

eg.
Friend1
Friend2
Friend3

I'd like to be able to quickly search all the users where the Friends field contains Friend2.

I've found FIND_IN_SET but that only works for commas and the data can contains commas and foreign characters.

Obviously searching with regular expressions and the such will be slow. I'm new to the whole cross referencing so I'd love some help on the best way to structure the data so that it can be found quickly.

Thanks in advance.

Edit: Ok, I forgot to mention a point that the data is coming from a game where friends names are stored locally and there are no links to another users ID. Thus the strings. Every time they connect I am given a dump of their friends names which I use in the background to help match games.

Upvotes: 0

Views: 89

Answers (3)

Jim
Jim

Reputation: 22656

The most commonly used structure for this kind of data is usually adding an extra table. I.e.

user
    id,
    name
    email,
    e.t.c.

user_friend
    user_id
    friend_id

Querying this is a matter of querying the tables. I.e.

List all of a users friends names:

SELECT friend_id
FROM user_friend 
WHERE user_id = :theUser

Edit: Regarding OPs edit. Just storing the names is possible too. In this case the table structure would become:

user_friend
    user_id
    friend_name

and the query:

SELECT friend_name 
FROM user_friend 
WHERE user_id = :theUser

Upvotes: 2

hjpotter92
hjpotter92

Reputation: 80639

My database hasn't been filled yet so I can easily change the format and structure in which the data will be stored.

Yes, you need to normalize your database a bit. With current structure, your searches will be quite slow and consume more space.

Check out this wiki for detailed help on normalization.

You can have the friends table and users table separate and link them both by either foreign key constraint or inner joins.

The structure would be:

Users table

  • id: AUTO_INCRMENT PK
  • name
  • other columns

Friends table

  • id: AUTO_INCREMENT(not required, but good for partitioning)
  • UserID:
  • FriendsID
  • DateAdded
  • OtherInfo if required.

Upvotes: 1

centree
centree

Reputation: 2439

Why are you keeping friend names as text? This will be inefficient to edit uf say a user removes a friend or changes their name. That's another thing, you should store friend names by some auto_increment id key in your database. It's much faster to search for an integer than a string, especially in a very large database. You should set up a friends table which is like 

 Column 1: connectionid auto_increment key
 Column 2: user1id int
 Column 3: user2id int
 Column 4: date added date

ect...

Then you can search the connection table above for all rows where user is user1id or user2id and get a list of the other users from that.

Upvotes: 1

Related Questions