CODEWITHSUNDEEP
mysqlarrayssql-like
M Smith
M Smith

Reputation: 430

Storing 'array' in SQL column and retrieving all rows WHERE certain element is in said array?

I currently have an SQL table listing all the different conversations between the users of a messaging app. This table contains a column titled participantIDs which contains the user ID of each of the (two) parties in the conversation. These values are separated by a comma, for example 19,25 would denote that the conversation was between user 19 and user 25.

Now, I am trying to work out how to retrieve the relevant conversations when a user looks at their messages inbox. To do this, I expect I will need to use a line of code such as

SELECT * FROM `convos` WHERE `participantIDs` LIKE (*contains user id*);

What would be the correct way to write this query?

Upvotes: 0

Views: 192

Answers (2)

bobflux
bobflux

Reputation: 11581

This is a really, really bad way to do it. As a.slimane says, normalize your database!...

The gist of it is that:

select messages from Personne where idSender = '1' and idRecipient = '2';

...will be fast, since you will create an index on (idSender, idRecipient) which will allow to find the rows with a direct index lookup.

However, there is no way to index a search on "part of a column contains a value". This means MySQL will have to scan many rows (potentially the whole table if you really insist) and it will be terribly slow.

Well, there is one way to index search on "part of a column contains a value": FULLTEXT. But this does not apply here.

Upvotes: 1

Slimane amiar
Slimane amiar

Reputation: 1044

instead of having one column separated by a coma ,i am suggesting you a simple way, you could create a table called "Personne" with a schema (id, idSender, idRecipient, messages) , and to select all message bbetween perssonne 1 and perssone 2 , you use this request select messages from Personne where idSender = '1' and idRecipient = '2';

in this way you will respect the first normal form as explicated here Normalization of Database

Upvotes: 2

Related Questions