On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote: > > Ananda Kumar schrieb: > >> 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, >> > > are this fields in same order as in table?
no not in the same order DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d >> '> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d >> > > AUCT_START_DATE, AUCT_END_DATE > > why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d > H:i:s ? Good catch, mysql gives in the y-m-d H:i:s format %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL) >> -> AGAINST('BOOKS') > 0 and >> > > why "> 0" ? I need to check with developer CURNT_PRICE_USD between ifnull(null,0) and >> ifnull(null,999999999999) limit 1000; >> > > CURNT_PRICE_USD between 0 and 999999999999 Also need to check with developer, Just guessing, they want for prince between 0 and 99999999999 why ifnull(null, ...) ? > > >> +----+-------------+---------------+----------+--------------------------+--------------------------+---------+------+------+-------------+ >> | 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) >> >> > -- > Sebastian Mendel >