Hello everyone,
I am in the process of building a search engine on a database. I have two
tables that have a many-to-many relationship: A story table and a category
table.
Story Table:
-----------
id | story|
-----------
1 | Alice in Wonderland
2 | Peter Pan
3 | Pokemon's adventure
4 | Tale of Two cities
5 | War and Peace
..etc..
Category Table:
--------------
id | category|
--------------
1 | Children
2 | Classics
3 | Tolstoy
..etc..
`
Storycategories Table:
----------------------
storyid | catid |
---------------------
1 | 1
1 | 2
2 | 1
2 | 2
3 | 1
4 | 2
5 | 2
5 | 3
..etc..
>From my (admittedly dim) understanding of SQL, this is how one is supposed
to organize a many-to-many relationship. So peter pan is listed as 1
(childrens) and 2 (classics).
I am writing a web interface to do searches for stories. In my web
interface, you have something like this:
---------------------------------------------------------------
[]Classics []Childrens []Tolstoy []Fiction ...
Search Categories:
(x) match all categories
( ) match any category
---------------------------------------------------------------
In order to build a query that matches ALL categories, I use aliasing. To do
a search on stories that are Children AND Classics, I make two instances of
each table as shown in the example below.
select
S.story
from
stories_tbl S,
categories_tbl C1, storycategories_tbl O1,
categories_tbl C2, storycategories_tbl O2
where
S.id = O1.storyid and O1.catid = C1.id and
C1.category like "Children" and
S.id = O2.storyid and O2.catid = C2.id and
C2.category like "Classic"
group by
story
This query works fine. My problem arises when I have many categories to
choose from. If I check many of the categories boxes, and select match all
categories, my program builds a query with a large amount of tables, I get
an error like this:
Error 1116: Too many tables. MySQL can only use 32 tables in a join
My guess is that there is a limit of 32 tables in a join, and that my use of
aliasing here ends up violating the 32 table limit.
Even worse is when I make a query that has a little less than 32 tables.
This won't give an error, but will leave a process running on the machine
that consumes 100% cpu. After a while, this process will render the database
unusable, and no one can use the database until the process is killed!
So to clarify, the query that ends up being built looks like this:
select
S.story
from
stories_tbl S,
categories_tbl C1, storycategories_tbl O1,
categories_tbl C2, storycategories_tbl O2
....
categories_tbl Cn, storycategories_tbl On
where
S.id = O1.storyid and O1.catid = C1.id and
C1.category like "Children" and
S.id = O2.storyid and O2.catid = C2.id and
C2.category like "Classic"
...
S.id = On.storyid and On.catid = Cn.id and
Cn.category like "xxxx"
group by
story
This query fails when "n" is around 16.
My question is, how can I build a query that will support an arbitrary
number of ANDs on a many to many relationship? I'd like to be able to do a
search on stories that are "Children AND Classics AND Tolstoy AND....
Category N" where N is fairly large (hundred or so)..
Is there any way to do this? Am I using aliasing incorrectly?
Those of you that have made it in reading this far, thank you :). I
appreciate your attention and any assistance you might lend me in this
matter.
Julien Beasley
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
