FULLTEXT namesearch(firstname,lastname)
You don't show the value for the key_buffer_size variable. This is very important for taking advantage of your indexes. Here is a link for the documentation:
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html
As for get the top three in each category. That's a tough one. You may try using a GROUP BY with the GROUP_CONCAT function. This would give you one line per group. And limit the groups returns by setting a max length for the group concat. Although this is a kludge since you are limiting the amount of text returned rather than the number of values concatenated.
The other way would be to just get all the matched rows, sorted and/or grouped appropriately, and then filtering the list with your front end (i.e. php). If the physical size of the text returned is not that large, it should process it fairly quickly.
On Aug 12, 2004, at 8:43 AM, [EMAIL PROTECTED] wrote:
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]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
