08-26-10 07:32 AM - Post#84488    

After update to FusionBB Version 3.0 FINAL my forum hungs up sometimes.
The logging of mysql's slow-queries shows that the problem with the search.
If it "Search in subject only" OR "Search in body only" query time is very small but if Search in subject and body it takes 10-40 seconds.

Code:



  SELECT p.post_id,p.topic_id,t.topic_first_post_id                 FROM fbb_posts AS p,                 fbb_topics AS t                 WHERE t.topic_id = p.topic_id                 AND p.is_moderated != 1                 AND (MATCH t.topic_subject AGAINST ('Drunken') OR MATCH p.post_body AGAINST ('Drunken'))                   AND p.forum_id IN ('6','25','39','139','22','37','135','141','87','137','126','138','136','81','108','106','128','142','145','146','5','17','45','19','27','7','24','42','35','93','94','95','97','98','100','147','111','8','46','33','34','61','58','72','86','38','88','10','26','70','62','29','40','15','68','16','49','78','32','75','12','103','79','71','119','13','48','14','64','65','67','66','63','76','102')                  && p.user_id NOT IN('')                 ORDER BY p.post_posted DESC                 LIMIT 200; 

I think there is problem with indexes
I try on MySQL 5.0.4 and 5.0.88

09-01-10 04:48 AM - Post#84513    

    In response to kit400

I found the problem. There was not an index.

Code:



 ALTER TABLE fbb3.fbb_posts ADD FULLTEXT ( post_subject );
Query OK, 829943 rows affected (9 min 26.80 sec)
Records: 829943  Duplicates: 0  Warnings: 0