Using foreign keys and cascades

[John Adams]

My background is MSSQL as well, and imo, your bro should have no difficulty seeing how things currently work - but like me, freaking out that MySQL is so limited in what it can do comparitively. MySQL works for what we're doing, and it's free. Two great perks. I'm sure many a DBA have reviewed this beast in the past, yet here it stays.

My interested would be in getting it faster, rather than worrying about orphaned data or ID ranges. But you'd be amazed how well MySQL runs on a dedicated FC4 box doing nothing but service MySQL to the Emu. Time to buy more hardware.

[Rhodan]

Well, here's the first thing he came up with. It shows all the orphaned waypoints in the database.

select a.*
from grid_entries a
left outer join grid b on a.zoneid = b.zoneid and a.gridid = b.id
where
b.zoneid is null
order by
zoneid, gridid, number

I got 231 orphaned records in the ax_peq18d database.

I'll have to see what else he came up with during the day while I was at work.

[sesmar]

Originally Posted by Rhodan

I got 231 orphaned records in the ax_peq18d database.

That is not that bad. Where you really want to look is the Spawn Groups.

SELECT
  spawngroup.id,
  spawnentry.spawngroupID,
  spawnentry.npcID,
  npc_types.name
FROM
  spawnentry
  LEFT JOIN spawngroup
    ON spawngroup.id = spawnentry.spawngroupID
  LEFT JOIN npc_types
    ON npc_types.id = spawnentry.npcID
WHERE
  spawngroup.id IS NULL
ORDER BY
  npc_types.name

4187 Records. And if you also look at the number of them that have a null NPC ID as well. That is more surprising.

Some other things that I have been looking into to help increase performance is Indexing; however, the current indexing seems to be what is needed.

[fathernitwit]

just to answer questions... we tend not to use transactions, stored proceedures, foreign keys, constraints and cascading deletes because honestly... mysql sucks at being a "grown up" database... plus most of them are rather new features anyhow.. if we desired a strong DB, we would use postgres.

mysql is fast, and what makes it fast is that it dosent support (or at least didnt used to) all of the features listed above. I do agree that external utilties is the safest way to "clean" the DB of crap which might build up over time. That said, nobody is running a server (and likely ever will) on a large enough scale to make cleaning the DB really matter.

[John Adams]

@ Rhodan -- neener, told ya so.


Missed me? I'm baaack!