Hello All,
 
Has there been any consideration to optimising the database especially in relation to tracking site visits and page views?
 
I have about 160MB of a database used on tracking and wondered if there could be a function in the admin to clear out tracking data older than say 3 months or whatever.
 
Regards
David.

Yes, i'm surprised this function isn't available. Something where you can do a .csv dump and refresh it to clear it out. You could then have the information backed up on the drive.

Yes - an essential function.

 
I would say that the reporting system on here should be all re-thought. on some pages, it takes so much time to load what's going on just by clicking on the page. For instance. My store has over 100k items, so when i click on product report... it literally is looking to collect inverntory information for every item that I sell just by going to the page.

I am not sure if this is possible for anybody else but have you considered connecting to the whole viart database structure via odbc. I did this in 5 or 10 minutes using access as the front end and linking tables rather than importing. Once the link is established all tables can be viewed and updated in real time so you quite easily create a query to delete any tracking data older than a certain date or for that matter use the same set up to create a bespoke product report or manipulate product, clients, orders etc etc.
 
The downside is you need microsoft access and also you would need to download and set up myodbc and configure the odbc administrator so everything work . (Another option would be to use open office which is free). Once done though it does open up the tables in the database for easier manipulation in some cases.
 
I see it as an extra function rather than a fix all.
 
All that said i am sure Viart could reduce the tracking data by adding a quick date function the system. It would only involve a quick delete query and a field to set the period when this should happen
 
Hope this helps

Any instructions on how to set this up?

Ok Dan,
 
Sorry I need to check something first. Are Viart hosting your site or is it on your own server. The reason I ask is that if on Viart's server they do not allow external access to the databases so it would be a non starter. If the database is on your own server you would need the following info to establish a connection..... I presume you are using a PC and not a Mac
 
Mysql Host - the IP or url the database is stored on
Mysql Database Name - (probably "Viart")
Username -
Password -
 
In addition you would need to download the latest version of "myodbc" from http://dev.mysql.com/get/Downloads/Connector-ODBC/3.51/mysql-connector-odbc-3.51.23-win32.zip/from/pick#mirrors - choose the top option "http"
 
Once all this in place let me know and will try and guide you through the next stage.
 
At this point the options are to use microsoft Access if you have it or open office (which is free) if you don't.

I have downloaded the file. Yes, I have my own server. Thanks so much for the help thus far .
 
Now what?

Do you have Microfoft Access?

Here are some instructions I had stored. I have modified them so they are relevant to this project.
 
Please bear in mind I am not an expert but this is something I have learnt over the years.
 
1). Install myodbc by unzipping the file you downloaded and clicking on the resulting set up file.
 
This should only take 30 seconds max
 
2). Once installed you need to open the “odbc data source administrator” which is part of the windows package . I use XP and the easiest way to find it is to type odbc in “help and support” accessible via the start menu. It may be different for other platforms
 
3). Click on the resulting “odbc data source administrator” link
 
4). A screen will open
 
5). By default you will be on the “user dsn” tag
 
6) Click “add”
 
7). Scroll down and select the “MYSQL ODBC 3.51 Driver”
 
8) . A mysql “Connector ODBC” box will appear
 
9). You will now need to enter your details, the first 2 can be anything of your choice so it is recognisable later.
 
e.g
 
Data Source Name– My Viart Link
Description - bla bla
Server – Either IP address or domain for site
User – xxx
Password - xxx
 
If all is correct you should see database name in the drop down list at the bottom, click on “Viart” and click test.
 
If success ….click ok and the data source will be saved on the system so you can move onto the next stage.
 
 
Microsoft Access or Open Office
 
1). Open access and create a brand new database saving it to your desktop or in a folder of your choice. I called mine “Viart Link”
 
2). Once created open it up and go to file >>>>>get external data >>>>link tables.
 
3). The next screen is slightly confusing but all you need to do is choose odbc Databases() from the files of type list at the bottom
 
4). After doing so a box will appear and this is where you select the odbc data source connection you created earlier.
 
5) Select the tab at the top “machine data source” and select the data source connection created earlier.
 
Click OK and all the tables for Viart should appear.
 
You can now select all or just one of the tables.
 
Please Note: Only a link is created and you cannot edit the tables structures but you can edit data in the tables
 
In a nutshell: You can administer the whole site via this method. Be warned though unless you are familiar with how relational database link together don’t try anything too ambitious for starters.
 
all the best