"Simple" many to many relationship - MySQL

[Soft Pack]

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.

[MindSpace]

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.

[Modifier]

Do you have index on:
Doc.ID
Rel.DocID
Rel.TagId
Tag.TagId
?

[kelfro]

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).

[Modifier]

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).

[Soft Pack]

kelfro, Propeller develops recommended that:

$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

$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

HAVING COUNT( d.ID )=3

to

HAVING COUNT( d.ID ) >= 3

thus one can also use wildcard searches (eg, a search on 'data%' and 'database' fail without'>').

[Soft Pack]

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