|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
improve performance on FULLTEXT search.
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 mysqlexplain 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_SLD, -> BID****,AUCT_DURTN_DAYS,AUCT_TYPE_CDE,SUCCESS_YN,B IN_SLD_FLAG,BLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLER Y_FEE_FLAG, -> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_F EE_FLAG,DATE_FRMAT(AUCT_START_DATE,'%Y-%m-%d '%H:%i:%s'),DATE_FRMAT(AUCT_END_DATE,'%Y-%m-%d %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL) -AGAINST('BKS') 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) |
![]() |
| Viewing: Web Development Archives > Mailing Lists > MYSQL > improve performance on FULLTEXT search. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|