Hi again, I worked on the problem and found a way to make the LOAD INDEX INTO CACHE work on my main tables now.
I actually found out that some of my indexes using varchar could be optimized because they aren't used for search but ordering (alphabetical and such). So having a index length of 255 on this index isn't really useful, and I tuned it so that it's 24 chars long. (I didn't put more because it's a multiple index with other fields type). Then all my indexes are using 1024 block size now, and the LOAD INDEX INTO CACHE works like a charm, showing much improved performance with all the RAM available. Anyway, thanks again for all your explaination. It's always good to know how things work in the core. I can better handle how I index my tables knowing I want to put all their indexes in cache at start. Happy programming ! On Mon, 21 Feb 2005 21:46:13 +0100, Sergei Golubchik <[EMAIL PROTECTED]> wrote: > Hi! > > On Feb 21, HMax wrote: > > Thank you for your answer Sergei, > > > > It's all clear now, and I'm glad to know where the problem comes from. > > > > Now if I understand correctly, my only solution is to manage to create > > indexes in my tables that ALL have the same block size (1024). This > > would mean reducing the size of the indexes on my Varchar fields, > > which I think I can. What is the max characters I should use when > > indexing my Varchar so that block size are 1024 ? (if possible of > > course). I have no idea how to calculate this. > > You'd better try with trial-and-error. > myisamchk -dvv shows block size. It's enough to create an empty table > and run myisamchk -dvv on it. > > The formula is in mi_create.c but I would spend more time unrolling all > the defines and deriving max varchar langth, that you would do with > trial-and-error :) > > > And is there absolutly no way to force 1024 block size even for > > varchar ? > > No, but you can make it 2048 for normal indexes. > Block length is a multiple of myisam_block_size, so if you set it to > 2048, all indexes will use it. (of course it'll be suboptimal for > everything but the long varchar keys. And even if you have an index over > VARCHAR(255), actual values are usually shorter, right ?) > > Regards, > Sergei > > -- > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer > /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany > <___/ www.mysql.com > -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
