The speed of DDS is, though, quite slow and is impacted depending on the amount of physical .EML files there is to check.
The following procedure will enable a significant increase in the speed of DDS by adding an index to the database. Under normal circumstances, it is only possible to perform it on an external database due to need to access the database as a Database Administrator (this is not possible for the internal database). To access the internal database would require extra 3rd party software such as DatabaseBrowserPortable (archived here). So if you are running the internal database and do not have such software then stop reading now. However, access with DatabaseBrowserPortable is possible and the same tweak can be made (you will need to decrypt the database password from your hmailserver INI file to access the internal database. See footnote below**). If you want the benefit of easy access to Administration of the database in the future, you might also consider upgrading to an external database - see here for a guide; the internal is small and against EULA for commercial applications. And you cant do things like this to it.
!!! DO NOT ATTEMPT THIS PROCEDURE IF YOU ARE NOT COMPETENT or familiar with database administration. You can totally break the SQL database !!! You have been warned.
EXAMPLE OF BENEFIT
On my system there are 33,000 Email files. A DDS check takes 10 minutes.
With this index in place, it takes only 33 seconds.
That is a significant increase, I think you'll agree.
PROCEDURE
In this tutorial I am using and referring to the MySQL database (version 5.5). The SQL commands will be the same with MSSQL and PostgreSQL databases but the access to them databases will be different. As the database Administrator, you are responsible for knowing how to access this. Some installations may also have a GUI equivalent (an Administration Console GUI) such as Workbench (for MySQL) and of course the index could be added using that (and not the command line method, below). Of course, being an experienced and confident administrator of your database, you will know what is best for you to perform the index creation (and deletion) according to these guidelines.
Note: The stage of actually running DDS is stage (5) below.
1, cd to the MySQL bin directory (eg, C:\Program Files\MySQL\MySQL Server 5.5\bin )
2, Launch the MySQL command line administration prompt with following command (where 'root' and 'password' are your access details to your database
Code: Select all
mysql -uroot -ppassword
3, Connect to your database (where "Hmaildata" is the name of your database):C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.5.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
Code: Select all
connect Hmaildata
4, Create an index on the messagefilename column:mysql> connect Hmaildata
Connection id: 146
Current database: Hmaildata
Code: Select all
CREATE INDEX tmp_hm_messages_filename ON hm_messages (messagefilename);
This has now created an index.mysql> CREATE INDEX tmp_hm_messages_filename ON hm_messages (messagefilename);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
5, At this point you can run your DDS.
6, It is advisable to then delete the index after you have completed the DDS run.*
MySQL:
Code: Select all
drop index tmp_hm_messages_filename on hm_messages ;
Code: Select all
DROP INDEX hm_messages.tmp_hm_messages_filename
Code: Select all
DROP INDEX tmp_hm_messages_filename;
*Note: adding such an index to the tables could in theory be detrimental to performance on heavy load (busy) servers as it means the index is checked and updated for each and every email record write/change (incoming, outgoing, moving to a folder, being deleted - they all involve writing and deleting from the hm_message table and the index). For this reason, it is advisable to delete the index. However, you may test and leave the index in place and then monitor your system (if you feel you might be a light user) to see if there is any noticeable difference or benefit. (You never know, it might also improve it. It remains untested either way.)mysql> drop index tmp_hm_messages_filename on hm_messages ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
** FOOTNOTE:
To access the internal database using DatabaseBrowserPortable you wll need the database password:
- a, open your HMAILSERVER.INI file and select the 'password' string under
b, Run hmailsever/Addons/Utilities/DecryptBlowfish.vbs and where prompted enter the string copied. This will create and display a short string of characters (this is the password). Copy this string of characters.[Database]
Password=a2bd344fc6608264d2d6b3ca2f89303
c, Run DatabaseBrowserPortable, and choose 'Connections - Add' and point to your internal Data directory database (remember to choose MSSQL CE as database type). Enter "Hmailserver" as the User name and the above decrypted string as the password.