Clean up the DB?! // Datenbank bereinigen?!

Rahner · Post by **Rahner** » Sun 13. Aug 2006, 20:57

In my Database are tons of articles and cats which dont exist any longer, I don't want to clean up by hand. It's too complex.
Is there anything which helps me to clean up my Database with the "240 articles" (in use are only ~100)?

/////////////

Meine Datenbank quillt langsam etwas über...längst nicht mehr vorhandene Artikel sind dort noch wiederzufinden...gibt es etwas um das ganz etwas zu bereinigen?

greetz Rahner

terry_b · Post by **terry_b** » Sun 13. Aug 2006, 21:45

Hi Rahner,

That is something I am waiting for.

It would be great to have something like the File center's trash can where you can see a list of all the articles/content part deleted.

Terry

Post by **flip-flop** » Sun 13. Aug 2006, 22:09

Hi,

there are table columns like trash or deleted. If they are filled with e.g. 999 this row is signed as deleted.
Have a look:
http://faq.phpwcms-docu.de/category8.html (german)
http://faq.phpwcms-docu.de/category9.html (english)

Knut

Rahner · Post by **Rahner** » Mon 14. Aug 2006, 16:17

flip-flop wrote:Hi,

there are table columns like trash or deleted. If they are filled with e.g. 999 this row is signed as deleted.
Have a look:
http://faq.phpwcms-docu.de/category8.html (german)
http://faq.phpwcms-docu.de/category9.html (english)

Knut

Dort finde ich leider nichts zu meinem Problem...oder übersehe ich da was?

Post by **flip-flop** » Mon 14. Aug 2006, 16:32

Das sind hinweise auf die Verarbeitung der Daten.
In der DB suchst du die tabelle die bearbetet werden soll. -> Ansicht -> und nun die Spalte Trash oder delet (999 oder 99999) oder so. Das sind die Zeilen, die gelöscht werden könen, da sie als GELÖSC'HT markiert sind.

Rahner · Post by **Rahner** » Mon 14. Aug 2006, 16:47

Danke!
Habs gefunden!
Also alle Tabellen in denen die Spalte Trash nicht existiert, gibt es auch keinen Datenmüll?

Post by **flip-flop** » Mon 14. Aug 2006, 16:53

Zumindest nicht für uns ersichtlich.

Post by **Oliver Georgi** » Mon 14. Aug 2006, 16:57

In aller Regel ist der Wert nicht 999 sondern NUR 9.

Hier die passenden Queries dazu:

Code: Select all

DELETE FROM phpwcms_article WHERE article_deleted=9;
DELETE FROM phpwcms_articlecontent WHERE acontent_trash=9;

Üblicherweise sehe ich aber keinen Vorteil darin Daten final aus dem System zu löschen - nicht solange wir über lediglich 100 Datensätze reden. Bei 100.000 könnten wir noch mal drüber reden.

Oliver

terry_b · Post by **terry_b** » Mon 14. Aug 2006, 17:04

Hi Oliver,

Do you plan on including a function like I described? or something else about the deleted articles?

Thanks,

Terry

Post by **Oliver Georgi** » Mon 14. Aug 2006, 17:21

I do not plan to integrate such function. This is always something only db admins should be able to do. I have seen too many problems with deleting important stuff from db with just 1 simple click.

Oliver

Rahner · Post by **Rahner** » Mon 14. Aug 2006, 17:57

Thanks Oliver for the snippet, two seconds too late...
I managed it manually

pico · Post by **pico** » Mon 14. Aug 2006, 21:41

Hi
@OliverG
just a suggestion - make Database-Table(s) like phpwcms_article_trash and etc. move the deleted Articles etc. to this Tables - so the 'Original' Tables will keep clean and if a User is shure to remove the Trash Files it is a easy 'drop Table'
and with a little bit more Code a 'Recover deleted Structures/Article will be easy.
I know that this is not realy nessercy - but will be a nice Feature.

Post by **Oliver Georgi** » Mon 14. Aug 2006, 22:34

makes no sense. I don't see any advantage.

Oliver

kubens · Post by **kubens** » Tue 15. Aug 2006, 01:27

Pico's suggestion is good and from my point of view it make sense.

From view of a database there is a big difference between 100 or 100.000 rows in a table. But this depends how you are working with such a table. Every sql statement will parsed and optimized. If the optimizer recognize that a table contains an index which can be used for the sql statement then the index will used. But in case that there is no adequate index then the optimizer will initiate a full table scan. This means he will load every row from the table. Pending on the memory which is assigned and the size of the tables this is something which can not be handled in memory, this means that we produce I/O and this is the worst and slowest case. For this reason it make definitely sense to ensure that in a production environment is no unnecessary trash or something else.

But I fully aggree with you about the issue that often important information is gone by just one click and that this is something which occurs sometimes and of course just on a sunny Friday

However on the other hand we must ensure that the response time of our server is as short as possible, especially durring peaks. If I can decided which is more important, then I would do everything to improve my performance and of course I will ensure that we have at least a daily backup of the production environment

Currently my biggest phpWCMS installation contains 970 articles (270 of them are in trash) and 1676 contentparts (375 of them in trash). Yes it works, but when I will reach the limits of my little server, when my visitors will boring because they are waiting for a requested page more then x seconds? We will see

Just my two cents worth

Br
Wolfgang

Post by **Oliver Georgi** » Tue 15. Aug 2006, 08:09

I know this argument is coming. But really: makes no "real" sense. And I have many other things to do. And these are more important in my eyes. Before implementing this it's better to implement an archiving solution.

If you think you have tons of "maked as deleted" stuff in your db remove it yourself. Optimizing the index might help too.

Oliver