Bukkit/CraftBukkit — comment on commit ea5efcf1d548392de7c26ad493a6ba53a2a60d73
File:  line 0
By Maescool:

again a bit offtopic.. but somehow related to why the lag is there..

@salamanda Primary's are mostly used for auto_increment id's
what i did is added an index on the columns that are used in where statements in SELECT, UPDATE and REMOVE queries.
You can add multiple columns to 1 index (if you have multiple where statements), that optimizes the mySQL and makes it process a lot faster..
i think also that plugin developers should use INSERT DELAYED more if it is a big stream of inserts.. like placing blocks..

a good tip: keep an eye on SHOW FULL PROCESSLIST; and see what queries are running slow.. an other option is to log slow queries..
that you can do in the mySQL-server configuration (I'm a linux user.. so i'm used to do such things..)
I didn't have taken the time yet to check HawkEye yet to see how optimal that one is.. but i noticed that WhoPlacedIt was not optimized at all.. and made my server lag majorly..

before i migrated to mysql it was on sqlite.. and it lagged also.. but i'm not really used to work with sqlite.. so migrating it to mySQL and figuring out what was lagging.. was a major ++