Notice: CMSB v2.60 Beta 1 (Jan 30, 2014)

Hi Dave,

Do you recommend a particular type for the multi value list fields (VARCHAR, MEDIUM...) ?

Thanks again!

Djulia

Hi everyone,

I've updated the ZIP file with a bugfix for the problem that Djulia discovered, so if you downloaded it previous to this message, please download it again! The only file changed is cmsAdmin/lib/admin_functions.js

Thanks for your excellent detective work, Djulia!

EDIT: I've also attached the file here so it's easier to update.

Djulia, 

Do you recommend a particular type for the multi value list fields (VARCHAR, MEDIUM...) ?

You could experiment, but I don't think multi-value list fields are going to see much of a performance boost, so I'd just leave them as auto - or if you know the max length of all the values will be under 255 then varchar(255).  

Multi-value list fields (not single value list fields) are something I'd like to revisit in a future release actually, and store them differently.  Perhaps in a separate table, because while the current method "works", it does let us do as many things as we'd like to through direct MySQL queries.

@Chris - It is perfect! Thanks for your reactivity!

@Dave - Do you think that CMSB can manage a index on multiple columns?
It is right for curiosity. It is not an immediate need.

Thanks!

Djulia

Hi Dave, Chris and I.T. staff,

I'm not completely understanding the indexing issue with the new MySQL but what I gather is that it's a faster way to retrieve specific records or types of records in which specific columns of data are tagged or annotated...is that correct?  If I'm close then I have a client this might work really well for but I'd have to hire you guys to do the modification. Let me give you the scenario and if it's a good fit I can ask Ross for a bid today and pay for the work to get on the pile right away.  My client has about 2-300 customers that she maintains extensive data on. She teaches them and puts each lesson under their membership account, complete with photos, embedded video links, homework and drills.  So, each lesson gets text data entered, anywhere from 1-30  high res photos (but I've shown her reducing the final output size to be more reasonable is helpful and she's complied), video embeds (I talked her out of loading up video directly to her site because it eats up bandwidth and storage and she can have a youtube or vimeo channel for free), and the lesson will pull in drills and points to remember from other sections of the database (ie: "drills" section, "remember" section, maybe "gallery" section).  Each day when she's putting in new lessons for her customers, or modifying some of the previous lessons, she states categorically it's easier and faster for her if she can have the backend database listings page display 1000 records at a time.  While the server is fairly responsive, and CMSB runs very fast, it is still a shared server and so it does bog down at specific times daily.  I've shown her that reducing the listing size to 50, 100 (even 200) and using the search form is exponentially faster during those hard periods but she's adamant to doing it the way she wants (and the customer is always right).  I've witnessed, at this condition, the lessons section take anywhere from 14 seconds to 3 minutes to have the listings page display; whereas the other sections take a fraction of a minute between .14 and .43 seconds.  She also is adamant that up until 3 mos. ago it wasn't bogging down so much.  I take her word at that so if something changed it's most likely a change/challenge on the hosting company's end...either the shared server has simply gone past it's peak and is bogging down, or maybe something happened to it's processes and it's bogging down.  I've shown her specifically how much faster reducing the number of records loading up on the listings screen will keep it running fast...but she won't do it.  So I'm grateful for the timing of the hosting company to move our hosted sites to a brand new set of equipment and update to more recent, and faster versions, of MySQL and pHp (and they give the client the choice of versions so if, for example, she wanted/needed to use M 5.2 with Zend optimizing, that's no problem.  So, in your opinion would setting up the user accounts/lessons sections, or columns, in the database take advantage of this powerful indexing feature?

Thank you kindly!

@Djulia - The easiest way would be to manually add or remove indexes with the "MySQL Console" plugin.  We're probably going to be doing that more and more on custom jobs here so we might add some more support for that in future.  Maybe a way to delete indexes from the "Advanced" tab or create them with the MySQL query on that page.  And then support to store them and re-create them from the CMSB schema files.  Let me know any ideas as they come up!

@Equinox - A lot of things could be happening.  The simplest and easiest place to look first is the hosting.  Is she sharing a hosting with all your other clients?  And what kind of hosting plan is it?  It might be worth asking the host to see if they can tell which sites are using all the resources at those busy times.  If another site is using all the server resources it won't matter how efficient we make her site, the server just won't have the resources to server it up quickly.  If the server move is happening soon I'd wait until after that's been done and see if there's still issues, then we could look for the cause at that point.  Let me know if that works.

Thanks!

Attached is screenshot of my v2.60 speed test. I haven't researched what it all means yet, but am open to comments related to my low speed improvement numbers.

~ Deborah

Hi Deborah, 

Actually I'd say those numbers are great.  Even a 100% increase in speed (which would be 2x) is a huge improvement.  I actually get lower numbers on my workstation than on our live website, and it's likely to do with the MySQL configuration, memory & cpu limits, etc.   So the performance is going vary from server to server, and when needed we can research further, but as far as having a checkbox option that can increase the speed of a query, without having to do anything else, I'm still really happy with those numbers.

Out of curiosity, though, do you know the details on your hosting?  For example is shared, virtual private server, dedicated server, internal, or cloud hosting?  And are you on Windows or Linux?

Thanks for posting your results!