no there is no index building or alter doing on.
On 6/13/08, mos <[EMAIL PROTECTED]> wrote: > > At 11:38 AM 6/12/2008, you wrote: > >> Hi Sebastian, >> >> I tried to order the column as close as possible to the table structure >> and >> removed all the formatn command and if conditions, but still it take 3 min >> > > Are you sure when you are running the fulltext search, the table isn't > locked because you are building the index or altering the table? > > Mike > > > > select >> >> >> ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL, >> >> BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG, >> GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG >> from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and >> CURNT_PRICE_USD between 0 and 999999999999 limit 1000 >> >> >> >> On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> > >> > >> > >> > On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >> >> >> >> >> >> >> 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 ----will this have any performance impact. >> >> >> >> 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 >> >>> >> >> >> >> >> > >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >