Steven Roussey wrote: > > I guess the reason for this is that the I have a some blob fields > whitch are > > all used.. (each record consist of approx 600 KB...) > > There it is. MySQL's MyISAM will get the whole record even if it only > needs a part. You can try InnoDB as it does it differently.
Hi Steve, Yes I tried changing the table to InnoDB and I got a great speed at once... if the ProductionYear field was indexed. But on Non-indexed field it actually seemed to be a bit slover than the MyISAM. > [At some point I may try and add that capability to MyISAM. I've been > researching good ways to compress text and still search it in its > compressed state. Then I hope to add the ability of MyISAM tables to > automatically compress TEXT/VARCHAR/CHAR (non binary) fields without > worrying about uncompressing everything if someone does a LIKE on that > field. BLOBs can use gzip type compression. And neither the data nor > decompression should occur if the field is not used. When I get more > time...] Yes.. This sound like a nice feature. :-) > However, back to your issue. Have you tried adding > INDEX(ProductionYear,ID)? That would prevent MySQL from having to read > the datafile at all, since it could find everything it needs in the > index file. My ID field has always been the Primary Key and I added ProductionYear as a INDEX. Like this INDEX(ProductionYear). I just tried adding the ID to that index as you suggest above and I got the desired speed right away :-))) And when I did an Explain select... the Type changed from "ALL" to "Index" :-) > My speed guesses: > > o If you didn't change the structure of the table or add an index, then > switching to InnoDB would improve performance quite a bit. > Tested & Verified :-) > > o Adding an index(ProductionYear) to the above InnoDB table would speed > things even more. > Tested & Verified :-) > o Adding INDEX(ProductionYear,ID) to the MyISAM table would be even > faster still. > Tested & Verified :-) > -steve- > > http://Network54.com > Thanks to all of you who have spent time in my problem... I now consider the problem as solved :-) If I want to have all my data in one table as inteded I have now received 3 possible solution to gain maximum speed. 1. Modify Index to INDEX(ProductionYear, ID) - and conintue to use MyISAM 2. Convert Table to InnoDB and Add INDEX(ProductionYear). 3. Split up the table and e.g. keep blob fields etc. in a separate table. I very mutch like the file structure used by MyISAM table compared to InnoDB and will therefore stay with the MyISAM Table Type :-) And I will Add the ID field to my ProductionYear Index. I will also as people have suggested split up my table moving my biggest field (Cover blob) to a separate table. I actially tested this: Before I added ID to the Index which required the select command to do a full scan I got a greate speed by just moving the Cover field to a separate table... and by adding the ID to the Index I gained another fraction of second :-) Again.. Thanks to you all for the support Kind Regards Thomas Kvamme [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
