[TYPO3-dev] improve "alias" indices on UTF8 sites

[R. van Twisk]

Martin Kutschker wrote:
> Steffen Kamper schrieb:
>   
>> Hi Masi,
>>
>> "Martin Kutschker" <Martin.Kutschker at n0spam-blackbox.net> schrieb im 
>> Newsbeitrag 
>> news:mailman.1.1165235593.14398.typo3-dev at lists.netfielders.de...
>>
>>     
>>> Hi!
>>>
>>> Some fields used for indices (eg aliases etc) are known to take only 
>>> 7bit-singlebyte data, yet Mysql has to deal with potentially 
>>> 8bit-multibyte data. Do you think that Mysql will access the indices more 
>>> efficient if these fields were marked as latin1? I think it may use less 
>>> memory for buffers etc.
>>>
>>> Masi
>>>       
>> is it possible that you think of the 7-byte roll-pointer, which is an 
>> additional field of MySQL ?
>>     
>
> No. All DBs that support UTF8 natively must allocate more than char-length 
> for utf8 as it uses a variable byte length. I think that for performance 
> reasons sort buffers use a fixed in-memory length. So this will be three 
> times the char length for utf8. This memory is wasted but could be claimed 
> for other queries as there is a maximum buffer size. So reducing the buffer 
> size for one query will increase the overall performance for parallel queries.
>
> Masi
> _______________________________________________
>   
Hey Masi,


I understand what you are saying...
I think the only way to try it out is trying to test it on let's
say 100.000-500.000 records and see if there is a difference.

It also depends how MySQL handles it internally, if every
character set is converted to utf-8 prior the search, sort etc..
then it might be a bit slower as well, I don't think this will
happen but there might be to much going on internally.

To do a good test the index should be at least big enough
to not fit into any of the MySQL buffers... so we can
really see a performance difference. Doing operations
one them (turn query cache of during the tests).


When I look at the average MySQL installation I also
don't think the average people will see a problem in this area
unless they do a large number of joins and have large tables.


It might be something for the performance tuning WIKI?


Ries



-- 
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/freelance-typo3.html
skype: callto://r.vantwisk