Something I had meant to ask a while ago but forgot: Why doesn’t the DB use triggers?
DELIMITER $$
CREATE TRIGGER characters_after_delete
AFTER DELETE ON characters
FOR EACH ROW
BEGIN
– Note: OLD is used to refer to columns effected by the trigger when updated or deleted
DELETE FROM item_instance WHEREowner_guid = OLD.guid;
DELETE FROM character_inventory WHERE guid = OLD.guid;
– Additional statements for other effected tables
END
DELIMITER;
Now I’m not saying this example is 100% accurate but wouldn’t this be simpler than having the core run multiple statements?
It would make more sense to use InnoDB and have foreign keys set properly in my opinion. Then you could simply set the keys to cascade on delete, effectively getting rid of any data depending on the entry.
Would make much more sense than writing triggers for all possible deletes in my opinion, and cleaner as well /emoticons/default_smile.png
I have already started adding Foreign Keys (not triggers) to the characters database.
This topic has been discussed in the dev section of the forums (3 years ago).
I also wanted to add FKs to world database however we are using MyISAM that does not support (yet?) FKs. Changing the engine of all tables in the world database to InnoDB means an increased loading time of 10x (your mileage will vary).
Which mysql version did you get such a high difference on Nay? In my experience InnoDB doesn’t have a very big performance penalty compared to MyISAM any more in MySQL 5.5 and higher. I haven’t tested with Trinitycore specifically though.
In my machine the difference was around 10x. In VincentMichael’s computer the difference was barely noticeable.
If you would like to give it a try start 3.3.5 worldserver with current database and take note of the loading times and then apply https://gist.github.com/DDuarte/02c39bcea6f410e262c9 and do the same. I’ll appreciate your results.
I thought this might have been discussed before but search returned nothing and there have been a couple forum resets over the years.
If we have mixed engines and they both support triggers, why not just use the triggers? Is the intent to use the FKs for something other than cascaded deletes? One of the drawbacks to cascaded FKs is that they do not activate triggers so it’s difficult to mix the two.
I refuse to add triggers because of mysql limitations.
characters database can use FKs without any problems, it’s only world db that can’t have them now but I guess that having FKs in characters is way more important than world