Mysql FULLTEXT optimization

Polyglot Projects Sphinx & Full-Text Search
1

i have a table of products with 200.000 products with full text search indexes
size of table is: 405 Mb, size of indexes is: 526 Mb

I would like to optimize search time, because my server load is very high.

my products table is not modified never, so i think the best way should be to use a memory engine for this table, but it doesnt support fulltext indexes,
can you tell me any other way to optimize my search?

here i have some info about index use and configuration:

my.cnf is:

mysql> show status like “key%”;
±-----------------------±------------+
| Variable_name | Value |
±-----------------------±------------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 6698 |
| Key_read_requests | 24950073645 |
| Key_reads | 527042599 |
| Key_write_requests | 36710021 |
| Key_writes | 2662162 |
±-----------------------±------------+
7 rows in set (0.00 sec)

this is my.cnf:

[mysqld]
init_connect=‘SET collation_connection = utf8_general_ci’
init_connect=‘SET NAMES utf8’
ft_min_word_len=3
key_buffer_size=1500M
open-files-limit=20000
query_cache_size= 64M
max_connections = 256
safe-show-database
skip-locking
key_buffer = 8M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size =8M
thread_cache_size = 8
delay_key_write = ALL
low_priority_updates=1
concurrent_insert=2
thread_concurrency = 8
wait_timeout = 90

Let me know if you will need more info

2

Actually, it is hard to give advice without knowing a lot about your schema, data, and the queries you run against it. But in general, as long as you can fit the data into memory, myisam + fulltext should work reasonably well. Most of our customers who can’t fit it into RAM end up with Sphinx + InnoDB instead.

3

i have much traffic at my site.
Queries are always different, so query cache not works.
i use key_buffer_size = 3G, to have indexed in memory.

but its still slow.

What will be the best way to load tables in memory to speed up access.

i can hold tables in memory without problems but how can i do it using myisam tables?

thank you