Why You don't use another schema:
Table A: (content table...currently nearly 40,000 rows and 62 MB)
id UNSIGNED INT PRIMARY
status VARCHAR 10 INDEXED
category VARCHAR 20 INDEXED
content LONGTEXT
wordstemmed LONGTEXT (fulltext index)
+ other fields
You have to fill wordstemmed field with cont
ults for "piano" would be cached.
So many variables to consider!
Thanks again for your input.
Mike.
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 9:25 PM
To: Mike Boone; [EMAIL PROTECTED]
Subject: Re: Optimizing Custom Full Text Index
Hi Mike,
Those tables aren't that big for what you're doing (which is about how
I'd do it if I wasn't using built-in full-text :-)).
How many results are your searches returning? How long are the queries
taking?
The C table: Do you need the index on content_id? For deletes or
something? Doesn't
: Wednesday, November 19, 2003 2:01 PM
To: Mike Boone; [EMAIL PROTECTED]
Subject: Re: Optimizing Custom Full Text Index
Have You tryed:
SELECT A.id, COUNT(A.id), A.category FROM A, C WHERE
C.content_id=A.id AND A.status='Active' AND (C.stem_word_id IN (10,20,30))
GROUP BY A.i
Have You tryed:
SELECT A.id, COUNT(A.id), A.category FROM A, C WHERE
C.content_id=A.id AND A.status='Active' AND (C.stem_word_id IN (10,20,30))
GROUP BY A.id
Santino
At 13:08 -0500 19-11-2003, Mike Boone wrote:
Hi all,
I have used PHP and MySQL for years but have never tackled the amount of
data
