Hello Nico, You are right that the order by takes a lot of time. But I tested the statement without the order by, and it still takes a lot of time. Tried to put an index on the field in the order by clause, but that didn't change much (creating the index took 22 minutes...). I found on a forum that when the storage engine is myisam, each row in a record is always read as a whole, even if the (blob)fields are not selected, causing bad performance. So I thought of changing to InnoDB already. But I will try to go to file based storage for binary fields first. Fingers crossed....
Thanks for your answer. Regards, Bart -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nico Klasens Sent: dinsdag 3 oktober 2006 16:36 To: Discussion list for designers (front-end) Subject: Re: [Users] Performance issue mmbase Hello Bart, Your issue is related to the order by clause. The MyIsam, blobs and order by are not a match in mysql. two solutions 1 remove the orderby attribute from the editwizard list 2 change the table type to InnoDB (huge speedups up to 1000x). Not all mysql distributions support InnoDB so check that first. Regards, Nico Bart De Man wrote: >Hello, > >We have an MMBase site running for about 3 years now. Over the last >several months, we have been encountering performance issues with >handling Documents. More specifically we encounter "Socket closed" >exceptions in the editwizard when trying to get the list of Documents. > >We have about 2500 records in the install_Documents table, which is >about 1GB in size. >We have found the problem to be the query that is generated by the >editwizards when calling the list of Documents: >SELECT >install_CN_Document.number,install_CN_Document.otype,install_CN_Documen >t.own >er,install_CN_Document.lastmodifiedTS,install_CN_Document.lastchangedby >,inst >all_CN_Document.createdTS,install_CN_Document.createdby,install_CN_Document . >title,install_CN_Document.description,install_CN_Document.mimetype,inst >all_C N_Document.filename,install_CN_Document.size >FROM install_CN_Document ORDER BY lastmodifiedTS DESC > >This query takes more than 90 seconds to run (on a fast and idle server). > >The problem might be related to the blob-fields in the table: although >they are not selected, they still seem to cause delay. > >We use mysql 4.0, MyIsam tables. > >Do you have any suggestions to a solution? >Can we for example switch to disk file based storage for these >Documents (=attachments). > >Thanks a lot, >Bart De Man > > >_______________________________________________ >Users mailing list >[email protected] >http://lists.mmbase.org/mailman/listinfo/users > > > > _______________________________________________ Users mailing list [email protected] http://lists.mmbase.org/mailman/listinfo/users _______________________________________________ Users mailing list [email protected] http://lists.mmbase.org/mailman/listinfo/users
