Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 07-04-2009
Member
 
Join Date: Oct 2008
Posts: 24
"Simple" many to many relationship - MySQL

This problem has annoyed me most of a day:
I have a simple tagging system, where you should be able to search the entities that have all keywords related (layout). Just a simple search, until it goes up for a join allows all rows from the table Doc just has one or more keywords. The next trial will be an inner join to an inner join on a MySQL server and is fine with until you make a couple hundred thousand relationships and looking at a fifteen to twenty words.

Has anyone had an idea for how to solve this problem? Has been for most of through google and immediately, I am not the only one having this problem.
Reply With Quote
  #2  
Old 07-04-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: "Simple" many to many relationship - MySQL

I have less experience with MySQL, so I do not know whether the same can be done there, but if I had the same problem on a SQL Server, then I would try myself to make an indexed view, representing a view of Doc Rel-Tag-relationship and an index of Doc.Content and Tag.Tag attributes.

Index would fill a lot of the disc, but would save a lot of time on your lookup when you could do to join all data for each query, and only do it when the index is created or updated.
Reply With Quote
  #3  
Old 07-04-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: "Simple" many to many relationship - MySQL

Do you have index on:
Doc.ID
Rel.DocID
Rel.TagId
Tag.TagId
?
Reply With Quote
  #4  
Old 07-04-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: "Simple" many to many relationship - MySQL

Code:
SELECT d.* 
FROM Rel r, Doc d, Tag t 
WHERE r.TagId = t.TagId 
AND (t.Tag IN ('ord1', 'ord2', 'ord3')) 
AND d.ID = r.DocID 
GROUP BY d.ID 
HAVING COUNT( d.ID )=3
Do not know if it works so? Found just a quick example and rewritten it to your name (hope that I had changed them properly).
Reply With Quote
  #5  
Old 07-04-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: "Simple" many to many relationship - MySQL

Quote:
Originally Posted by MindSpace View Post
I have less experience with MySQL, so I do not know whether the same can be done there, but if I had the same problem on a SQL Server, then I would try myself to make an indexed view, representing a view of Doc Rel-Tag-relationship and an index of Doc.Content and Tag.Tag attributes.

Index would fill a lot of the disc, but would save a lot of time on your lookup when you could do to join all data for each query, and only do it when the index is created or updated.
MySQL does not have materialized views (which is what a SQLServer indexed view really).
Reply With Quote
  #6  
Old 07-04-2009
Member
 
Join Date: Oct 2008
Posts: 24
Re: "Simple" many to many relationship - MySQL

kelfro, Propeller develops recommended that:
Code:
$tblAlias = self::getRndTblAlias(); 
$sql .= 'SELECT DISTINCT '.$tblAlias.'.DocumentId FROM'."\n"; 
foreach ($tokens as $token) { 
$token = mysql_real_escape_string($token); 
if ($firstToken) { 
$sql .= '(SELECT DocumentId FROM Support_Document_Tag JOIN (SELECT Id FROM Support_Tag WHERE Tag LIKE \''.$token.'\') Tags ON Tags.Id = TagId) '.$tblAlias."\n"; 
$firtToken = false; 
} else { 
$tblAliasPrev = $tblAlias; 
$tblAlias = self::getRndTblAlias(); 
$sql .= 'JOIN (SELECT DocumentId FROM Support_Document_Tag JOIN (SELECT Id FROM Support_Tag WHERE Tag LIKE \''.$token.'\') Tags ON Tags.Id = TagId) '.$tblAlias.' ON '.$tblAlias.'.DocumentId = '.$tblAliasPrev . '.DocumentId' . "\n"; 
} 
}
WHERE
Code:
$tblAlias = self::getRndTblAlias(); 
$sql .= 'SELECT DISTINCT '.$tblAlias.'.DocumentId FROM'."\n"; 
foreach ($tokens as $token) { 
$token = mysql_real_escape_string($token); 
if ($firstToken) { 
$sql .= '(SELECT DocumentId FROM Support_Document_Tag JOIN (SELECT Id FROM Support_Tag WHERE Tag LIKE \''.$token.'\') Tags ON Tags.Id = TagId) '.$tblAlias."\n"; 
$firtToken = false; 
} else { 
$tblAliasPrev = $tblAlias; 
$tblAlias = self::getRndTblAlias(); 
$sql .= 'JOIN (SELECT DocumentId FROM Support_Document_Tag JOIN (SELECT Id FROM Support_Tag WHERE Tag LIKE \''.$token.'\') Tags ON Tags.Id = TagId) '.$tblAlias.' ON '.$tblAlias.'.DocumentId = '.$tblAliasPrev . '.DocumentId' . "\n"; 
} 
}
This unfortunately gives some more joins, but can optimize the code so that the tokens are the fewest documents which have come first, and thereby reduce the number of posters considerably.

In your solution, it is advantageous to change
Code:
HAVING COUNT( d.ID )=3
to
Code:
HAVING COUNT( d.ID ) >= 3
thus one can also use wildcard searches (eg, a search on 'data%' and 'database' fail without'>').
Reply With Quote
  #7  
Old 07-04-2009
Member
 
Join Date: Oct 2008
Posts: 24
Re: "Simple" many to many relationship - MySQL

Quote:
Originally Posted by Modifier View Post
MySQL does not have materialized views (which is what a SQLServer indexed view really).
Yep, there are index of all primary keys and foreign keys.

And I say thank you to you all. Next time I choose a DB can make intersects
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: ""Simple" many to many relationship - MySQL"
Thread Thread Starter Forum Replies Last Post
Failed to install bluetooth drivers for "PCI Simple Communications Controller" with ASUS P8P67 deluxe mbangali Motherboard Processor & RAM 6 19-08-2011 11:02 PM
<input type="button" value="Enregistrer" onclick="location.href=../asp/PRaces.asp"> Luz Software Development 6 18-05-2010 12:27 AM
Simple Graphics program code for "C" Rup_me Software Development 3 09-12-2009 08:33 AM
Mysql error message "Unknown column in on clause" Carnie Software Development 3 21-11-2009 11:03 AM
XP Home SP2 OEM "Use Simple File Sharing" is not available hariharan_00 Windows Security 3 20-07-2008 05:06 AM


All times are GMT +5.5. The time now is 09:54 AM.