Howto: Database optimizing

[ralle030583]

The database currently contains: 522.714 killmails

IM COMMING FOR YOU EVE-KILL!!

stop fetching them eve-kill (sry coudnt resist)

[LaMaH]

The database currently contains: 522.714 killmails

IM COMMING FOR YOU EVE-KILL!!

stop fetching them eve-kill (sry coudnt resist)

im not
does eve-kill's feed even work btw.?..

i never seemed to be able to make anything fetch from it

[ralle030583]

should work if you do it for an corp / alliance
http://eve-kill.net/?a=feed&corp=Vanishing%20Point.

and that happens if you dont give anything to the feed:
http://eve-kill.net/?a=feed

Lear how to use this feed or move on...

[hux]

Is there an updated, final script to do all these changes? I haven't updated since 324, will do a clean 33x install these days and plan to use the db optimizing as well. As far as I understand, 33x is still not the version with the new installer, so I guess those db changes haven't made it to the svn yet.

[beansman]

If you wait a couple of days, it will all be in a brand new shiny installer

[hux]

Will work on my patience. Just asking myself now if those changes to the DB that will come with the new installer will cause problems with existing DBs. I mean... if I reinstall, got innoDB etc afterwards, and than try to import the MySQL dump... wonder if that works. Or if the changes to the existing data will be made on the fly.

[beansman]

As long as it's pure inserts, no problemo!

We are not changing table layout... (yet )

[FriedRoadKill]

So I was cruising through my DB after trying this stuff out on my test server and I noticed some inv_pilot fields pointing to kill ID 0 (a void id). This also applies to a few other tables. I checked it out somewhat, and my working theory is that mails that malform still write some information to the DB - causing data to be written that will never be seen.

So, seeing as it's the DB optimisation thread, why not also include some cleansing methods?
Here's some sql statements that checks if anything is referencing kill id 0, and deletes it.

Code: Select all

delete from kb3_kills where kll_id = 0;
delete from kb3_inv_detail where ind_kll_id = 0;
delete from kb3_inv_crp where inc_kll_id = 0;
delete from kb3_inv_plt where inp_kll_id = 0;
delete from kb3_items_destroyed where itd_kll_id = 0;
delete from kb3_items_dropped where itd_kll_id = 0;

Please to check this on your testbeds first before deploying to your lovely large kill DBs . Now I've run this on my DBs, and I see no ill effects yet, just need somebody else to confirm before the masses apply it. This also isn't a comprehensive list - there may be are others like it.

[Seahorse]

should work if you do it for an corp / alliance
http://eve-kill.net/?a=feed&corp=Vanishing%20Point.

and that happens if you dont give anything to the feed:
http://eve-kill.net/?a=feed

Lear how to use this feed or move on...

Error getting XML data from http://eve-kill.net/?a=feed&year=2008&week=41&gz=1

[nutter]

So I was cruising through my DB after trying this stuff out on my test server and I noticed some inv_pilot fields pointing to kill ID 0 (a void id). This also applies to a few other tables. I checked it out somewhat, and my working theory is that mails that malform still write some information to the DB - causing data to be written that will never be seen.

So, seeing as it's the DB optimisation thread, why not also include some cleansing methods?
Here's some sql statements that checks if anything is referencing kill id 0, and deletes it.

Code: Select all

delete from kb3_kills where kll_id = 0;
delete from kb3_inv_detail where ind_kll_id = 0;
delete from kb3_inv_crp where inc_kll_id = 0;
delete from kb3_inv_plt where inp_kll_id = 0;
delete from kb3_items_destroyed where itd_kll_id = 0;
delete from kb3_items_dropped where itd_kll_id = 0;

Please to check this on your testbeds first before deploying to your lovely large kill DBs . Now I've run this on my DBs, and I see no ill effects yet, just need somebody else to confirm before the masses apply it. This also isn't a comprehensive list - there may be are others like it.

hi ya iv just done it on my db and np probs yet m8 thanx dude