http://cerb4.com/forums/showthread.php?p=1760#post1760

Pancho also requests some form of attachment cleanup. (same thread)

http://cerb4.com/forums/showthread.php?t=338

May want to update this thread when this or CHD-1112 is completed. Both deal with address deletion (or how to better hide unwanted addresses).

http://www.cerb4.com/forums/showthread.php?t=311

An improvement on the query shown above:

SELECT a.id,
count(m.id) as hits,
count(r.address_id) as req_hits,
a.email,
a.contact_org_id
FROM address a LEFT JOIN message m ON (a.id=m.address_id)
LEFT JOIN requester r on (r.address_id = a.id)
WHERE a.contact_org_id = 0
GROUP BY a.id
HAVING hits = 0 AND req_hits = 0
ORDER BY req_hits DESC

This makes sure that you are not inadvertently removing an address that is listed as a requester on a ticket.

By removing the line WHERE a.contact_org_id = 0 you can also remove addresses that have an organization assigned to them, but no tickets.

However, I would not remove that as there is probably a reason why the contact (address) has an organization assigned to it.

And if you're happy with the results from the above query you can execute the following query to delete the addresses:

DELETE FROM address where id in (
SELECT ID from (
SELECT a.id,
count(m.id) as hits,
count(r.address_id) as req_hits,
a.email,
a.contact_org_id
FROM address a LEFT JOIN message m ON (a.id=m.address_id)
LEFT JOIN requester r on (r.address_id = a.id)
WHERE a.contact_org_id = 0
GROUP BY a.id
HAVING hits = 0 AND req_hits = 0
ORDER BY req_hits DESC
) AS d
)

This would be really useful in the maintenance task.

Thanks
Robert

Well testing the query on my 200,000 ticket DB it doesn't seems to like this query very much. I am guessing that limits are going to have to be added to the query to limit the result set.

Thanks
Robert

I'm no expert, but the query worked for me.

I don't know if this is relevant, but we are using Cerberus v.4.2.1 stable, build 914

Did it help when you added limits?

Actually I am still using 891 v4.1. But it isn't the query is error out it is that the query is timing out. I think I have to many records for the query to complete.

Thanks
Robert

Sam,

I am working on this some this weekend turning this into a plugin so this can be fixed for good. I have a question? Why is your select including a.email,
a.contact_org_id when they are not getting used?

Thanks
Robert

Warning don't run this query if you have a lot of messages in your system. It will grind you system into the ground.

Thanks
Robert

Hi Robert,

I used the extra select statements in the query as I was writing the query and simply used it so that I could manually go through the results for comparison.

I just forgot to take them out when I wrapped the query in a DELETE statement... sorry about this.

Also - we have a relatively small database, so I did not pick up that the query was slow.

Again - sorry about that, hope you can find a better query for that.

I would be interested in looking at the plugin when complete.

Regards,

Sam

Sam,

Although I will likely rewrite using sub query they seem to be a lot faster you might want to note these changes to the base query. Mysql will do a lot less work in this case.

DELETE FROM address where id in (
SELECT a.id
FROM address a
LEFT JOIN message m ON a.id = m.address_id
LEFT JOIN requester r ON a.id = r.address_id
WHERE a.contact_org_id = 0
AND m.address_id IS NULL
AND r.address_id IS NULL
ORDER BY a.id DESC
)

That last query the select section ran with the following on my live site.

8,072 total, Query took 859.6044 sec

Were as

SELECT a.id,
count(m.id) as hits,
count(r.address_id) as req_hits,
a.email,
a.contact_org_id
FROM address a LEFT JOIN message m ON (a.id=m.address_id)
LEFT JOIN requester r on (r.address_id = a.id)
WHERE a.contact_org_id = 0
GROUP BY a.id
HAVING hits = 0 AND req_hits = 0
ORDER BY req_hits DESC

Would time out.

It is a pretty cool change when you add an index to message.address_id. Query time before.

8,072 total, Query took 859.6044 sec

Query time after.

8,074 total, Query took 0.0203 sec

Now I need to double check something there was a change in 4.3 that might break this query need to double check.

Thanks
Robert

OK I have it all working. I am creating the wiki stuff for it right now.

http://wiki.cerb4.com/wiki/Purge_Contact_Addresses

Enjoy.

Thanks
Robert

Requested here. Point #3.
http://www.cerb4.com/forums/showpost.php?p=13162&postcount=1