Need help with delimited field query

[lxndr]

I'm wondering if anyone can help me with a MySQL problem I have. The first thing to say is that I am limited in the changes I can make to the database so basically have to work with what I've inherited. The problem I have is with a library system written in php that allows searches to be made of the types of documents that are held within the library. The existing search routine works fine but there is now a requirement to allow the search results to consist of ONLY those documents which have not yet been read by the user.  There are basically two main tables involved:

 

library document table: indexed by rec_id which is unique and numerical.

 

user table, contains a text field entitled docread which holds all the rec_ids of the documents which have been read by the user, stored in a delimited form, e.g.  1654|7653|543|  and so forth.

 

Initially, I was able to get the following to work by adding this to the existing query

 

$sql .= " AND rec_id NOT IN ({$exclude_list}) ";

 

where the $exclude_list variable is a list of all the document ids separated by commas.

 

Unfortunately, further investigation revealed that it was only working for some users and not others. I looked at a number of the user records and discovered that some of them had viewed over 6,000 documents so I guess the above is not the way to do it and likely to produce an enormous SQL query.

 

Given the structure of the rest of the existing software I don't really want to do down the route of solving this via PHP and was wondering whether there was a way of tackling it using MySQL directly. My own experience with MySQL is very limited so I was wondering whether anyone had any constructive suggestions. I'm not in a position to do away with the delimited field though.

 

 

 

[kicken]

user table, contains a text field entitled docread which holds all the rec_ids of the documents which have been read by the user, stored in a delimited form, e.g.  1654|7653|543|  and so forth.

 

That is the wrong way to go about it.  You should have a table which links them together, eg:

create table user_read_docs (
  userId int,
  docId int
);

 

That table would have one row for every document the user read. If desired you could store additional info there such as date read, number of times, etc.

 

With that setup you can exclude read docs using a left join on that table, such as

select
   *
from docs d
left join user_read_docs rd on d.docId=rd.docId and rd.userId=1245
where
  rd.docId is null

 

[lxndr]

That is the wrong way to go about it.  You should have a table which links them together, eg:

 

I already explained that that's the way it is and that I don't have the possibility to change it.  Secondly, with 50,000 users and say an average of 3,000 documents viewed each that's a table with 150 million rows!

 

 

 

[fenway]

150M rows isn't a big deal.

[lxndr]

150M rows isn't a big deal.

Academic really as I'm not at liberty to change the existing database structures as I pointed out in my original post. I was looking for a way of resolving the issue with them as they are. If it can't be done then so be it.

 

 

[fenway]

Then you'll need to filter out the "seen" documents afterwards.

 

Or, you could use a temporary table to allow for SQL queries to work propertly.

 

Or, you can abuse FIND_IN_SET().