delete orphan records form database

[imported_smile]

I think, perhaps I could use SQL script to delete the orphan data in database? Then gallery2 would not get slower and slower. Anyone knows how to make it?

The gallery2 has integrity check module that checks the db but does not repair it.

Last Run Details:

Checked 175 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 387, 388, 390, 391, 392, 727, 728, 729, 730, 731, 732, 733, 734, 735, 789, 790, 791, 792, 793, 794, 795, 796, 797, 850, 851, 852, 853, 854, 855, 856, 857, 858, 1166, 1167, 1168, 1169, 1170, 1171, 1172, 1173, 1174, 1590, 1591, 1592, 1593, 1594, 1595, 1596, 1597, 1598

First I need a sql to view the orphan records
Then sql to delete the orphan records.

See attached files here: http://www.dbforums.com/mysql/...abase.html#post6429988
I attach my AccessMap, AccessSubscriberMap SQL tables here.
I tried to wrte it myself but I'm not a programmer so...

I also attach my fixed tables (I deleted by hand)
Thanks for your help.

 

[GeekDrew]

This query should do what you're looking for:

DELETE
FROM `g2_AccessMap`
WHERE `g_accessListId` NOT IN (
SELECT `g_accessListId`
FROM `g2_AccessSubscriberMap`
)

That said, gallery2 should definitely *NOT* be getting any slower because of this issue.

 

[imported_smile]

Well that works 🙂

Perhaps I should use SELECT DISTINCT?

I wonder why my nickname changed from smile to imported_smile? After forum upgrade?

 

[ForumMaster]

This query should do what you're looking for:

DELETE
FROM `g2_AccessMap`
WHERE `g_accessListId` NOT IN (
SELECT `g_accessListId`
FROM `g2_AccessSubscriberMap`
)

That said, gallery2 should definitely *NOT* be getting any slower because of this issue.

i would change this query a little. assuming this db is oracle, and that "g_accessListId" is indexed, what you should do is change the NOT IN to NOT EXISTS. this allows the query to use indexes. a small change, but it can really impact performance. thus, the query should be:

DELETE
FROM `g2_AccessMap`
WHERE `g_accessListId` NOT EXISTS (
SELECT `g_accessListId`
FROM `g2_AccessSubscriberMap`
)

use the SQL EXPLAIN plan option to see how oracle build your query. then optimize it. you may be building it wrong. this query will run much faster because it uses an index and doesn't have to perform a full index scan. if you don't have an index, then it doesn't matter as much, but add one and performance will improve.

 

[Markbnj]

Well that works 🙂

Perhaps I should use SELECT DISTINCT?

I wonder why my nickname changed from smile to imported_smile? After forum upgrade?

smile, a number of accounts were brought over as imported_username. I'm not sure why, but it looks like the kind of thing a developer would code to work around some conflict. Post a message in Technical Forum Issues and they will fix it for you.

 

[ebaycj]

i would change this query a little. assuming this db is oracle, and that "g_accessListId" is indexed, what you should do is change the NOT IN to NOT EXISTS. this allows the query to use indexes. a small change, but it can really impact performance. thus, the query should be:

DELETE
FROM `g2_AccessMap`
WHERE `g_accessListId` NOT EXISTS (
SELECT `g_accessListId`
FROM `g2_AccessSubscriberMap`
)

use the SQL EXPLAIN plan option to see how oracle build your query. then optimize it. you may be building it wrong. this query will run much faster because it uses an index and doesn't have to perform a full index scan. if you don't have an index, then it doesn't matter as much, but add one and performance will improve.

That's only on oracle. And who uses oracle? Douchebags, that's who.
(SQL Server DBA here 😉 )



You could also do:

DELETE am
FROM AccessMap as am
FULL JOIN AccessSubscriberMap as asm
ON am.am_id = asm.am_id
WHERE (asm.am_id IS NULL)

 

[ForumMaster]

That's only on oracle. And who uses oracle? Douchebags, that's who.
(SQL Server DBA here 😉 )



You could also do:

DELETE am
FROM AccessMap as am
FULL JOIN AccessSubscriberMap as asm
ON am.am_id = asm.am_id
WHERE (asm.am_id IS NULL)

i would say the other way around. i use mostly oracle DB's and to my great sorrow, a few SQL server databases. i despise sql server. sure it's practical for a small database, but almost all our databases our quite large and it's quite annoying. why can't they emulate the to_char of oracle properly? i have to use datepart which is hardly an equal replacement...

 

[GeekDrew]

i would say the other way around. i use mostly oracle DB's and to my great sorrow, a few SQL server databases. i despise sql server. sure it's practical for a small database, but almost all our databases our quite large and it's quite annoying. why can't they emulate the to_char of oracle properly? i have to use datepart which is hardly an equal replacement...

How's it quite annoying, other than it not having a few features that you're used to in Oracle?