Using Match Against in a normalised Database (3 Tables)

[vze29xhy]

I have searched the existing responses in this forum and others and
could not find a solution.
I have a database on MySQL server Version "4.0.13". My table structure
is as follows
TABLE1:MusicCD
This table has three columns - UPC, Title, ExtendedTitle

TABLE2: Artist
This table has three columns - ArtistID, ArtistName, ArtistOtherName

TABLE3: CDArtist
This table has two columns - ArtistID, UPC

I am using this structure because I can have a many to many
relationship between CD and Artist, i.e. one CD may have more than one
Artist and one Artist may have more than one CDs.

My Full Text Indexes are created on
TABLE1 -> (Title and ExtendedTitle)
TABLE2 -> (ArtistName and ArtistOtherName)

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results. I must be doing
something wrong here.

Here is my query - (I need to search for ALL Music CDs whos title or
Extended Title contain the word "Vally" and the ArtistName or
ArtistOtherName contain the word "Mike") (Both conditions should
match.

Select m.* FROM MusicCD m, Artist a, CDArtist c WHERE
MATCH (m.Title, m.ExtendedTitle) AGAINST ('Vally') AND
MATCH (a.ArtistName, a.ArtistOtherName) AGAINST ('Mike') AND
(a.ArtistID = c.ArtistID AND c.UPC = m.UPC)

I would really appreciate if someone can point me to the right
direction. I am not sure where to go from here. This solution cannot
be implemented because of its speed and I need to implement some
solution, which is atleast reasonable.

Thanks in advance
Danis

[vze29xhy]

Anyone ??
I really need help in this one.

Thanks
Danis

vz******@verizon.net (vze29xhy) wrote in message news:<76**************************@posting.google. com>...

I have searched the existing responses in this forum and others and
could not find a solution.
I have a database on MySQL server Version "4.0.13". My table structure
is as follows
TABLE1:MusicCD
This table has three columns - UPC, Title, ExtendedTitle

TABLE2: Artist
This table has three columns - ArtistID, ArtistName, ArtistOtherName

TABLE3: CDArtist
This table has two columns - ArtistID, UPC

I am using this structure because I can have a many to many
relationship between CD and Artist, i.e. one CD may have more than one
Artist and one Artist may have more than one CDs.

My Full Text Indexes are created on
TABLE1 -> (Title and ExtendedTitle)
TABLE2 -> (ArtistName and ArtistOtherName)

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results. I must be doing
something wrong here.

Here is my query - (I need to search for ALL Music CDs whos title or
Extended Title contain the word "Vally" and the ArtistName or
ArtistOtherName contain the word "Mike") (Both conditions should
match.

Select m.* FROM MusicCD m, Artist a, CDArtist c WHERE
MATCH (m.Title, m.ExtendedTitle) AGAINST ('Vally') AND
MATCH (a.ArtistName, a.ArtistOtherName) AGAINST ('Mike') AND
(a.ArtistID = c.ArtistID AND c.UPC = m.UPC)

I would really appreciate if someone can point me to the right
direction. I am not sure where to go from here. This solution cannot
be implemented because of its speed and I need to implement some
solution, which is atleast reasonable.

Thanks in advance
Danis

[vze29xhy]

Anyone ??
I really need help in this one.

Thanks
Danis

vz******@verizon.net (vze29xhy) wrote in message news:<76**************************@posting.google. com>...

I have searched the existing responses in this forum and others and
could not find a solution.
I have a database on MySQL server Version "4.0.13". My table structure
is as follows
TABLE1:MusicCD
This table has three columns - UPC, Title, ExtendedTitle

TABLE2: Artist
This table has three columns - ArtistID, ArtistName, ArtistOtherName

TABLE3: CDArtist
This table has two columns - ArtistID, UPC

I am using this structure because I can have a many to many
relationship between CD and Artist, i.e. one CD may have more than one
Artist and one Artist may have more than one CDs.

My Full Text Indexes are created on
TABLE1 -> (Title and ExtendedTitle)
TABLE2 -> (ArtistName and ArtistOtherName)

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results. I must be doing
something wrong here.

Here is my query - (I need to search for ALL Music CDs whos title or
Extended Title contain the word "Vally" and the ArtistName or
ArtistOtherName contain the word "Mike") (Both conditions should
match.

Select m.* FROM MusicCD m, Artist a, CDArtist c WHERE
MATCH (m.Title, m.ExtendedTitle) AGAINST ('Vally') AND
MATCH (a.ArtistName, a.ArtistOtherName) AGAINST ('Mike') AND
(a.ArtistID = c.ArtistID AND c.UPC = m.UPC)

I would really appreciate if someone can point me to the right
direction. I am not sure where to go from here. This solution cannot
be implemented because of its speed and I need to implement some
solution, which is atleast reasonable.

Thanks in advance
Danis

[John]

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John
vze29xhy wrote:

Anyone ??
I really need help in this one.

Thanks
Danis

vz******@verizon.net (vze29xhy) wrote in message news:<76**************************@posting.google. com>...

I have searched the existing responses in this forum and others and
could not find a solution.
I have a database on MySQL server Version "4.0.13". My table structure
is as follows
TABLE1:MusicCD
This table has three columns - UPC, Title, ExtendedTitle

TABLE2: Artist
This table has three columns - ArtistID, ArtistName, ArtistOtherName

TABLE3: CDArtist
This table has two columns - ArtistID, UPC

I am using this structure because I can have a many to many
relationship between CD and Artist, i.e. one CD may have more than one
Artist and one Artist may have more than one CDs.

My Full Text Indexes are created on
TABLE1 -> (Title and ExtendedTitle)
TABLE2 -> (ArtistName and ArtistOtherName)

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results. I must be doing
something wrong here.

Here is my query - (I need to search for ALL Music CDs whos title or
Extended Title contain the word "Vally" and the ArtistName or
ArtistOtherName contain the word "Mike") (Both conditions should
match.

Select m.* FROM MusicCD m, Artist a, CDArtist c WHERE
MATCH (m.Title, m.ExtendedTitle) AGAINST ('Vally') AND
MATCH (a.ArtistName, a.ArtistOtherName) AGAINST ('Mike') AND
(a.ArtistID = c.ArtistID AND c.UPC = m.UPC)

I would really appreciate if someone can point me to the right
direction. I am not sure where to go from here. This solution cannot
be implemented because of its speed and I need to implement some
solution, which is atleast reasonable.

Thanks in advance
Danis

[John]

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John
vze29xhy wrote:

Anyone ??
I really need help in this one.

Thanks
Danis

vz******@verizon.net (vze29xhy) wrote in message news:<76**************************@posting.google. com>...

I have searched the existing responses in this forum and others and
could not find a solution.
I have a database on MySQL server Version "4.0.13". My table structure
is as follows
TABLE1:MusicCD
This table has three columns - UPC, Title, ExtendedTitle

TABLE2: Artist
This table has three columns - ArtistID, ArtistName, ArtistOtherName

TABLE3: CDArtist
This table has two columns - ArtistID, UPC

I am using this structure because I can have a many to many
relationship between CD and Artist, i.e. one CD may have more than one
Artist and one Artist may have more than one CDs.

My Full Text Indexes are created on
TABLE1 -> (Title and ExtendedTitle)
TABLE2 -> (ArtistName and ArtistOtherName)

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results. I must be doing
something wrong here.

Here is my query - (I need to search for ALL Music CDs whos title or
Extended Title contain the word "Vally" and the ArtistName or
ArtistOtherName contain the word "Mike") (Both conditions should
match.

Select m.* FROM MusicCD m, Artist a, CDArtist c WHERE
MATCH (m.Title, m.ExtendedTitle) AGAINST ('Vally') AND
MATCH (a.ArtistName, a.ArtistOtherName) AGAINST ('Mike') AND
(a.ArtistID = c.ArtistID AND c.UPC = m.UPC)

I would really appreciate if someone can point me to the right
direction. I am not sure where to go from here. This solution cannot
be implemented because of its speed and I need to implement some
solution, which is atleast reasonable.

Thanks in advance
Danis

[vze29xhy]

Thanks for a response John,

Here is the result of Explain:
Please note that ind_TitleAB is the name of my full text Index on
Table1, which includes both title fields.

table type possible_keys key key_len ref
rows Extra
------ -------- ------------------- ----------- -------
----------- ------ -----------
m fulltext PRIMARY,ind_TitleAB ind_TitleAB 0
1 Using where
c ref UPC,ArtistID UPC 10 m.UPC
11
a eq_ref PRIMARY PRIMARY 8
c.ArtistID 1 Using where
John <no****@toplevel.tld> wrote in message news:<zp*******************@newsread2.news.atl.ear thlink.net>...

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John

[vze29xhy]

Thanks for a response John,

Here is the result of Explain:
Please note that ind_TitleAB is the name of my full text Index on
Table1, which includes both title fields.

table type possible_keys key key_len ref
rows Extra
------ -------- ------------------- ----------- -------
----------- ------ -----------
m fulltext PRIMARY,ind_TitleAB ind_TitleAB 0
1 Using where
c ref UPC,ArtistID UPC 10 m.UPC
11
a eq_ref PRIMARY PRIMARY 8
c.ArtistID 1 Using where
John <no****@toplevel.tld> wrote in message news:<zp*******************@newsread2.news.atl.ear thlink.net>...

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John

[John]

Are you querying on the same database as you originally posted about?
You said:

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results.
The "explain" output looks like it deals with a very small number of
rows and should run quickly.

John
vze29xhy wrote:
Thanks for a response John,

Here is the result of Explain:
Please note that ind_TitleAB is the name of my full text Index on
Table1, which includes both title fields.

table type possible_keys key key_len ref
rows Extra
------ -------- ------------------- ----------- -------
----------- ------ -----------
m fulltext PRIMARY,ind_TitleAB ind_TitleAB 0
1 Using where
c ref UPC,ArtistID UPC 10 m.UPC
11
a eq_ref PRIMARY PRIMARY 8
c.ArtistID 1 Using where
John <no****@toplevel.tld> wrote in message news:<zp*******************@newsread2.news.atl.ear thlink.net>...

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John

[John]

Are you querying on the same database as you originally posted about?
You said:

Now, when I do full text searches just for title in table 1 or just
for Artist in Table 2.. they are EXTREMELY fast. When I try to search
on a combination of Title AND Artist.. they are EXTREMELY SLOW. (I
have 1.14 million rows in Table 1 and 500K rows in Table2). Sometimes
the query takes even 10 minutes to return the results.
The "explain" output looks like it deals with a very small number of
rows and should run quickly.

John
vze29xhy wrote:
Thanks for a response John,

Here is the result of Explain:
Please note that ind_TitleAB is the name of my full text Index on
Table1, which includes both title fields.

table type possible_keys key key_len ref
rows Extra
------ -------- ------------------- ----------- -------
----------- ------ -----------
m fulltext PRIMARY,ind_TitleAB ind_TitleAB 0
1 Using where
c ref UPC,ArtistID UPC 10 m.UPC
11
a eq_ref PRIMARY PRIMARY 8
c.ArtistID 1 Using where
John <no****@toplevel.tld> wrote in message news:<zp*******************@newsread2.news.atl.ear thlink.net>...

Try sticking "EXPLAIN" in front of your SELECT and post the mysql
output. I bet one table is being text searched once per record matched
in the other.

John