Re: Why dont my query use the index keys?

[chandru]

Hi johan,
 you have allocated 4M for your sort buffer size is ok. So i feel that 
the query is loading more than 4MB of data so it is creating a temporary 
file to do a sorting. please try to put some logic into the query that 
you use to load the appropriate data. I find that the tag_id has a 
cardinality of  35921 which is low which tells that there are more 
records matching the condition

tm.tag_id IN (99, 10807, 20728, 447, 807)

you might have to put some logic to over ride the same. Even putting 
some partitioning can help you.

Regards,
Pradeep Chandru.



Johan Thorvaldsson wrote:
> mysql> show global variables like 'sort%';
> +------------------+---------+
> | Variable_name    | Value   |
> +------------------+---------+
> | sort_buffer_size | 4194296 |
> +------------------+---------+
> 1 row in set (0.01 sec)
>
> 18 sep 2008 kl. 08.05 skrev chandru:
>
>> Hi Johan,
>> the query is going for a temporary table creation  "Using temporary; 
>> Using filesort"
>>
>> In case your sort_buffer_size is too low please try increasing the 
>> same. Be cautious don't try to increase it massively, since it is a 
>> per thread memory allocation.
>>
>> please let me know what is your sort_buffer_size by using the command
>> show global variables like 'sort%';
>>
>>
>>
>> Regards,
>> Pradeep Chandru.
>>
>>
>> Johan Thorvaldsson wrote:
>
>
>
> ---------------------------------------------------------------------
> Montania System AB
> Halmstad
> http://www.montania.se
>
> Johan Thorvaldsson
> johan.t@stripped
>
> Kristinebergsvägen 17, S-30241 HALMSTAD
> Telefon [phone] | Fax [phone]
>
>


********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to 
Sify Limited and is intended for use only by the individual or entity to 
which it is addressed, and may contain information that is privileged, 
confidential or exempt from disclosure under applicable law. If this is a 
forwarded message, the content of this E-MAIL may not have been sent with 
the authority of the Company. If you are not the intended recipient, an 
agent of the intended recipient or a  person responsible for delivering the 
information to the named recipient,  you are notified that any use, 
distribution, transmission, printing, copying or dissemination of this 
information in any way or in any manner is strictly prohibited. If you have 
received this communication in error, please delete this mail & notify us 
immediately at admin@stripped