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
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
>>>
>>
>>
>
