- MODX Version: 2.2.10
- PHP Version:5.3
- Database (MySQL, SQL Server, etc) Version: 5.0
Hi All Modx gurus
Bit of a technical modx/SQL question here.
situation
Recently a client made a number of pages. When i say i number im talking about 20,000+ in the space of a couple of minutes. As you know this can take it toll on an SQL database especially when trying to read from it. Not to mention the amount of time the resource tree in the manager having a heart attack takes as it tries to refresh the tree.
Unfortunately deleting this many resources takes just as long as you can not delete a batch of resources unless they are sitting under a parent folder
(right click -> delete --> delete children too).
This was an easy task but trying to purge the deleted items permanently from the database did not go smoothly as it just could not handle the amount.
In the end i got rid of the resources using the snippet below
$removed = $modx->removeCollection('modResource', array('deleted' => true));
problem is, deleting the resources was a success but this has left all of the default content values in the template variables for the resources that were deleted in another table. See the screenshots attached to the post for indication (see table size differences)
My theory for the solution
As i only want to delete the rows in the
modx_site_tplvar_contentvalues table where the contentID does not exist in theory i can delete only what resource does not exist.
pseudo code example
DELETE * FROM modx_site_tplvar_contentvalues
WHERE modx_site_tplvar_contentvalues.contentid is not in modx_site_content.id
Basically it looks through the
contentID of
modx_site_tplvar_contentvalues and checks if the same value exist in
modx_site_content ID column and deletes them if the resource does not exist.
If anyone could give me a nice little query to give this a blast. Maybe limit it to
one row to start then i can batch it if works.
i have attached some screen grabs of the database if it helps
many thanks
Nad
