OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
improve performance on FULLTEXT search.

From: Ananda Kumar (anandklgmail.com)
Date: Thu Jun 12 2008 - 09:50:19 CDT

Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.

I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM

mysql> explain select
    ->
    ->
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
    ->
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
    ->
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
    '> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
    -> AGAINST('BOOKS') > 0 and CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,999999999999) limit 1000;
+----+-------------+---------------+----------+--------------------------+--------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+---------------+----------+--------------------------+--------------------------+---------+------+------+-------------+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | | 1 | Using where |
+----+-------------+---------------+----------+--------------------------+--------------------------+---------+------+------+-------------+
1 row in set (0.05 sec)