Re: Re[2]: [Mantisbt-help] Performance

[Glenn H. <thr...@ma...>]

On 30-Jan-06, at 4:30 PM, Mark Phillips wrote:

> On Jan 30, 2006, at 12:23 PM, Alexander Hybel wrote:
>
>>   You might take a look at this message by Anthony Cartmell:
>>   http://sourceforge.net/forum/message.php?msg_id=2193013
>>   Some indexes are proposed to add a bit of performance.
>>
>>   Some of the columns are already in an index.
>>   And  be  aware that any additional index might take noticeable  
>> extra
>>   space. But what's some extra space compared to good performance?
>>
>>   At least this should give you a hint where to tune a bit.
>
> Excellent link, Alexander, thank you. The difference is quite  
> noticeable.
>
> I do wonder: why are these indexes not created with the install?  
> Just concerns about space with unwary users?
>

   I looked through the list in the referenced link. With 1.0.0 we  
added about half the indices in the list. We didn't touch the lesser  
used tables like news.

   In the process of adding them, I used the MySQL explain command to  
see whether adding indices would make a difference. There are also  
some performance guidelines from MySQL that we looked at. One of  
these stated that adding an index on a field with a low number of  
possible values was not productive. The overhead of adding the index  
and updating it was not worth the potential benefit. It takes up  
space, both on disk and in memory, displacing cached indices that  
would be more valuable.

   Increasing your database caches would probably help.

   We'll probably review this again in the next release to trade off  
the number of indices vs the performance gain. Any input would be  
welcome.

   ... Glenn

-- 
Glenn Henshaw                     Logical Outcome Ltd.
e: thr...@ma...              w: www.logicaloutcome.ca
Mantis developer and user