Page 1 of 2
Clean up the DB?! // Datenbank bereinigen?!
Posted: Sun 13. Aug 2006, 20:57
by Rahner
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
Posted: Sun 13. Aug 2006, 21:45
by terry_b
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
Posted: Sun 13. Aug 2006, 22:09
by flip-flop
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
Posted: Mon 14. Aug 2006, 16:17
by Rahner
Dort finde ich leider nichts zu meinem Problem...oder übersehe ich da was?
Posted: Mon 14. Aug 2006, 16:32
by flip-flop
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.
Posted: Mon 14. Aug 2006, 16:47
by Rahner
Danke!
Habs gefunden!
Also alle Tabellen in denen die Spalte Trash nicht existiert, gibt es auch keinen Datenmüll?
Posted: Mon 14. Aug 2006, 16:53
by flip-flop
Zumindest nicht für uns ersichtlich.
Posted: Mon 14. Aug 2006, 16:57
by Oliver Georgi
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
Posted: Mon 14. Aug 2006, 17:04
by terry_b
Hi Oliver,
Do you plan on including a function like I described? or something else about the deleted articles?
Thanks,
Terry
Posted: Mon 14. Aug 2006, 17:21
by Oliver Georgi
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
Posted: Mon 14. Aug 2006, 17:57
by Rahner
Thanks Oliver for the snippet, two seconds too late...
I managed it manually
Posted: Mon 14. Aug 2006, 21:41
by pico
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.
Posted: Mon 14. Aug 2006, 22:34
by Oliver Georgi
makes no sense. I don't see any advantage.
Oliver
Posted: Tue 15. Aug 2006, 01:27
by kubens
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
Posted: Tue 15. Aug 2006, 08:09
by Oliver Georgi
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