Hi everybody,

Does anyone know if there's a tool or something to perform a db maintenance on my GTLinks db, I just checked and my db size its 2Gb, so I'll like to know if there's something I can do to prune out date records or something in order to prevent future issues.

If anyone can recommend me what to do I'll appreciate it.

Thanks in advance,

Jesus

Hi,

Which are you large tables? Is ClickTrack one of them? (you should be able to view their actual Mb size in phpMyAdmin)

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Hi Andy,

Here are the largest....

lsql_ESMonthly, 7.1 MB
lsql_ESDaily, 58.7 MB
lsql_SearchLog, 7.7 MB
lsql_SearchLogs, 9.2 MB
lsql_SSCount, 3.1 MB
lsql_SSSite, 1.5 GB

So I think lsql_SSSite its the one causing the DB grow

Thanks for helping me out with this.

Jesus

Mmm, well I would say its the lsql_SSSite one thats the issue =)

Is this for the plugin for stats? (can't remember who its by =))

Maybe worth purging out the data older than maybe a year? By the looks of it, there must be quite a few years worth of data in that single table ;)

The rest all look very small - so I wouldn't worry about those.

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Yes, that for the extra stats plugin.

I'll like to keep 2009 and 2010 stats, so how can I purge old data?

Jesus

I wouldn't know without seeing some sample data :)

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

What do you need?

Let me see what I can do and try to send some info for you.

Jesus

Hi,

Just an example of the table structure (a copy + paste should be fine) ... and then an example of the data from that table.

The main thing I'm looking for, is the "date" field, and the format of it :) (i.e is it yyyy-mm-dd, yyyymmdd, etc)

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Here we go... thanks

Here is the 1st and last record


ID Date Title Path GroupType GroupID IP Referrer
1 2007-01-28 Buscador, directorio y portal de Monterrey Nuevo L... http://www.mundoregio.com/ Null Null 189.157.112.212 Null
906053 2010-08-09 Agencias de autos en Monterrey, Nuevo Leon, Mexico... http://www.mundoregio.com/Economia_y_negocios/Auto... Null Null 189.156.136.73 http://www.google.com.mx/search?hl=es&source=hp&q=...

Jesus

Ok, what happens if you run this query (just wanna make sure it gives the correct results, before I tell you to run the DELETE function =))

SELECT DISTINCT(Date) FROM lsql_SSSite WHERE Date <= 2009-01-01

Does that give you a list of dates going back to whenever you started using this plugin, or does it also show results from 2009-2010 too?

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Hi Andy,

Sorry for my delay to answer you, I was not available yesterday.

Here are the results:

MySQL returns no value (no columns). (Query time 0.0785 seg)

Jesus

Mmm, I thought MySQL could handle dates like yyyy-mm-dd

Ok, heres another possible way of doing it :)

1) Add a new field to that table : DateSimple (CHAR(20)) - this may take a while - due to having such a large table! So probably best to do this via mySQLMan, and then "re-sync" the SSSite table
2) Run this command to populate it:


3) Run this command:


4) Then run that command again, but a little different:


..does that give any results?

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!