----- Original Message ----- From: "Pakó Géza" <[EMAIL PROTECTED]> To: "Jouni Hartikainen" <[EMAIL PROTECTED]> Sent: Saturday, October 11, 2003 7:03 PM Subject: Re: How to search by groups efficiently with MySql 4.0.15?
> how about: > > select record.* > from link > where group_id='x' > left join record on record.archive_id=link.archive_id > ? > > hope this helps > Geza Pako > > ----- Original Message ----- > From: "Jouni Hartikainen" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, October 11, 2003 9:09 AM > Subject: How to search by groups efficiently with MySql 4.0.15? > > > > > > Hi all. > > > > > > I have this kind of problem setup: > > > > 1. I have a bunch of records and I need to store some record specific data > > about each of them (date, owner etc). > > > > 2. I have a list of categories (=groups) in which a record may belong to > and > > I have to be able to store some group specific data (name, code, is group > > shown etc) for each group. > > > > 3. One record can be in multiple groups and a single group can contain > > multiple records (n..n relation) > > > > > > The question is: > > > > Which is the best database structure for this kind of situation that > enables > > me to _efficiently_ find records by groups using various conditions and > > MySQL 4.0.15? > > > > For example I should be able to find all records that belong to group 1 > and > > 2 and to one of the groups 3,4 and 5. (1 AND 2 AND (3 OR 4 OR 5)). > > > > > > Currently I have this kind of structure: > > > > Table record: > > +------------+---------+---------------------+ > > | archive_id | creator | created_time | > > +------------+---------+---------------------+ > > | 1 | 1 | 2003-10-10 21:47:35 | > > | 2 | 2 | 2003-10-10 21:47:35 | > > | 3 | 2 | 2003-10-10 21:47:35 | > > | 4 | 3 | 2003-10-10 21:47:35 | > > +------------+---------+---------------------+ > > > > Table group: > > +----+----------------------------------+-------+ > > | id | name | shown | > > +----+----------------------------------+-------+ > > | 1 | Group name 1 | 1 | > > | 2 | Group name 2 | 1 | > > | 3 | Group name 3 | 1 | > > | 4 | Group name 4 | 1 | > > | 5 | Group name 5 | 1 | > > +----+----------------------------------+-------+ > > > > Table link: > > +------------+----------+ > > | archive_id | group_id | > > +------------+----------+ > > | 1 | 1 | > > | 1 | 2 | > > | 2 | 1 | > > | 3 | 3 | > > +------------+----------+ > > > > > > This seems logical structure for me. The problem arises when I try to make > > queries to the structure. > > > > The best way I have figured out to find records by groups is to perform a > > query like this: > > SELECT link1.archive_id FROM link AS link1, link AS link2 WHERE > > link1.group_id=1 AND link2.group_id=2 AND > link1.archive_id=link2.archive_id; > > > > The query above gives me the archive_id 1 as it should. (Query means that > I > > want to find all records that belong to groups 1 and 2). > > > > As you can easily imagine, when there is a lot of both records and groups > > and when searching conditions are a bit more complicated than in my > example > > query, the join that is performed is pretty enormous. And the query string > > is huge too (and even worse hard to construct programmatically) > > > > So I'm asking is there any better way to either construct the database > > tables or to perform searching queries? > > > > > > Sincerely, > > > > Jouni Hartikainen > > [EMAIL PROTECTED] > > > > _________________________________________________________________ > > Add photos to your messages with MSN 8. Get 2 months FREE*. > > http://join.msn.com/?page=features/featuredemail > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
