Optimize database question

[jimbudd]

On Admin>Computers there is a button for OPTIMIZE DATABASE.  What exactly does this do, and should it be run on a regular basis?

[friscoeyeassociates]

I have sat on answering this one for a couple of days because I don't really have a great answer for what exactly that button does. I have never used it, nor have I been instructed to at any point by Crystal, So I don't believe this is something you should be doing regularly... All that said, I do have some familiarity with the principle of database optimization. The definition would be quite literal in that typically when optimizing a database you would be patrolling for new, more efficient ways to process data. Accomplished by rewriting queries and trimming fat within the database. As to which aspects of optimization Crystal is referring to with this button, I am not sure. Using the context of it's location, my guess would be the button scrubs through old client connections and removes dormant ones, but I am not sure, and doing so without knowing for sure could potentially take some of your clients offline and you may need to get them back on manually. Who knows, maybe someone from Crystal will enlighten us. I'm a tech nerd too, and I would like to know! I did'nt even know that button was there! lol

[nick]

As far as I can tell, that button runs mysql's optimize function on each table in your database. Optimizing tables works a lot like defragmenting your hard drive, and gives comparable increases in performance and decreases in database size (meaning you likely won't notice it unless your database is extremely large, you have really messy tables - not likely, or you have a really slow computer.)
If you want to try it out, just make sure you do as it recommends and back-up your database before you start. The process itself is not likely to cause any issues, but if it were to get interrupted for some reason it would corrupt your data somewhere (good luck finding where). I've run it multiple times (mainly for fun, we're a small office) and haven't seen any issues caused by it.

dev.mysql.com/doc/refman/5.7/en/optimize-table.html

The only time I have ever run into corruption on our database was after a power outage that knocked out the server mid-way through an exam (my fault for putting off buying a battery back-up). Pretty easy fix with regular back-ups.

Frisco "Using the context of it's location, my guess would be the button scrubs through old client connections and removes dormant ones" for that just double click any names on that grid that aren't supposed to be there. I have had a connection stick open a couple times after closing out of a patient's records. The records stay locked to other people even though nobody is actively in the chart. Going to the admin > computer screen shows two instances of my computer (only one instance of CrystalPM on my PC running at the time), one of which is still in the patient's chart. Double-clicking the stuck-open connection simply shuts it off. As far as "old client connections", any time you shut down the server all the connections are removed.

The other button, "Flush Database Settings", I think runs a flush privileges statement on the database. If that is what it does then it isn't really useful for anyone but server admins. Flush privileges reloads the grants table, so essentially it dumps some commonly used data from your RAM and then puts it back. Unless you are actually altering user privileges (not CrystalPM users, but the actual MySQL server), it is meaningless to run. I can't really think of anything else that button might do.

If I'm wrong about any of this, I hope someone will let me know so I can fix it.

Either way, the only thing that I have found useful on that page is the ability to drop/reset connections.