Re: How to optimize fulltext selection?

[Gleb Paharenko]

Hello.

> "Fulltext initialization"

Ok, now we exactly know that we should optimize the FULLTEXT part
of your query. I don't know the options which directly affects the speed
of the FULLTEXT searches, so I suggest you to play with key_buffer and
table structure. The ratio of Key_reads to Key_read_requests is good
so we don't need to increase the value of the key_buffer_size.

Check if the speed of the queries have changed after running OPTIMIZE
TABLE on `programms1` table. Please send the output of EXPLAIN for your
query after running ANALYZE TABLE. If the output of EXPLAIN and the speed
of the query has changed when you make the strings in the AGAINST clauses 
the same? Create a separate table (let's call it newtable) with id and 
search_keywords fields (linked to the programms1 on id). Make a FULLTEXT 
index on search_keywords and research if the speed of the query which 
retrieves the 'relevance' from the newtable has changed. Research how the
value of the PACK_KEYS  option affects the speed of the queries on your
old (I guess you'll perform tests on the copy of the original table) and
new table (with the structure which I've proposed). Create a separate cache
for indexes from the newtable and load indexes into it (I guess the memory
where FULLTEXT index is stored will be less fragmented). Create a separate
cache for the programms1 table and load indexes into it on startup
(for the same reason), however I'm not sure if you able to do this in 
production environment. The best is to perform all manipulations on the
test server with the same configuration. See:
  http://dev.mysql.com/doc/mysql/en/index-preloading.html
	http://dev.mysql.com/doc/mysql/en/multiple-key-caches.html



Michael Monashev <michael@stripped> wrote:
> Hello
> 
> GP> What state is SHOW PROCESSLIST reporting for this query?
> 
> "Fulltext initialization"
>  
> 
> Sincerely,
> Michael,
> http://xoib.com/ http://3d2f.com/
> http://qaix.com/ http://ryxi.com/
> http://gyxe.com/ http://gyxu.com/
> http://xywe.com/ http://xyqe.com/
> 
> 
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com