Hmmm... looks like it may be impossible to do fulltext searching on 3 million rows as
few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL 4.0.17 (plan
to upgrade soon.. will this help??) and I created the index that took 9 minutes to
process using phpmyadmin. I did notice that when I created the fulltext index that
the fulltext has field size of "1" when looking at the keys in phpmyadmin. More
specifically, it looks like this:
Keyname________Type____Cardinality____Action__Field
PRIMARY.......PRIMARY.....3237981.....product_id
search_text...FULLTEXT....3237981.....search_text..1
Also, would tuning mysql settings get the processing time down from 5-40 seconds to
something more reasonable like 0.3 - 1.0 sec? My current system variables are:
ft_min_word_len: 3
ft_max_word_len: 100
ft_max_word_len_for_sort: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_seeks_for_key: 4294967295
max_sort_length: 1024
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_sort_buffer_size: 8388608
query_alloc_block_size: 8192
read_buffer_size: 131072
sort_buffer_size: 89128952
tmp_table_size: 33554432
I think I've heard of people getting good results will fulltext in under a second with
3 million rows+ so I think its possible...
Help greatly appreciated,
- John
<<----------------------------------------------------
--------- Original Message --------------------
I am trying to do a fulltext search on my database of 3.3 million rows (~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I search for
'Mountain' I would like to see the top 3 results for each category. For instance
'mountain' might return:
Apparel
----------------
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food & Beverage
--------------------
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
--------------------
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
--------------------
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually wouldn't be fast since
there are about 20 categories. I decided instead to make a TEMPORARY TABLE, insert all
the matching product_ids into it, then group by category and limit for the top 3.
Unfortunately, the temporary table insert combined with the fulltext query takes much
too
long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM product_fulltext WHERE
MATCH
( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per category (not sure how I
will do that yet) or joined the products on any tables for images etc. I didn't set
any
LIMIT parameters because if I said "LIMIT 20", all 20 matches could be Apparel products
and the other categories would not show up. The BEST performance I can get is about 5
seconds on a single search term (as opposed to the 2 terms in 'United States')
I'm at a loss here...
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
