- Original Message -
From: Pak Gza [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]
