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 content stemmed words.
Table C dropped
Your query:
SELECT stem_word FROM B WHERE stem_word IN ('truck','piano','move');SELECT A.id, COUNT(A.id) as rows, A.category FROM A WHERE A.status='Active' and match( wordstemmed) against ( 'truck piano move') group by A.id HAVING rows=3;
or
SELECT A.id, COUNT(A.id) as rows, A.category FROM A WHERE A.status='Active' and match( wordstemmed) against ( 'truck piano move' in boolean mode) group by A.id HAVING rows=3;
In this manner there is no need to search in a 4.5 MRows Table and the fulltext engine does a lot of work for You and it will improved in the future.
I handle a DB with 50000+ rows of newspaper news and the full-text works fine: about 3 seconds per query if I sort by date/time about 0.5 second per query if I don't sort
There is only a small problem: delete are slow but I have a cron script that start at 1:00AM
(nobody uses the DB at that time) that stop indexes, perform deletes, restart index.
(There is a bug in MySql < 4.0.15 that corrupts indexes after a restart index so I do an alter table to rebuild indexes). This script takes about 6 Minutes.
Santino
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
