Howto: Database optimizing

[hux]

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;

If I understand this SQL stuff right, SELECT COUNT(kll_id) would result the number of rows that have the kll_id 0 for the first line. Tried this with all above lines you provided, but couldn't find a single 0-value. My board has nearly 10k kills and I had that malfunction bug often, so the problem seems to be not very common.

[beansman]

SELECT COUNT(kll_id) FROM &&&&&& WHERE kll_id = 0;

[Mike Wittman]

Since i put this optimization in sync'ing kills is extremely slow. This i don't really mind since i'll only have to pull them all once.

However it seems that the table is full or something because no more are sync'ing and the total rows in the table keeps jumping up and down in numbers and getting stuck at the number below. I also notice that under the heading Cardinality on the table structure screen this number appears for quite a few columns. Is this limiting how much info is put into the table or something?

Showing rows 0 - 29 (~25,654 total, Query took 0.0009 sec)

Also would someone be so kind to create php scripts that convert the database BACK to how it was originally. My backup failed for some reason.

[beansman]

Are you looking in phpmyadmin? The total has a ~(tilde) in front, meaning it's not exact!!! It's just an estimate taken from an info table in the database to make the query faster!

Because then you would have to go to the next page to see the next kills.

You can just reverse the scripts, i will look onm that later!

[jonas vinthyn]

I show two tables still as MyISAM, would it help to convert those over also? If so:

ALTER TABLE `kb3_dgmtypeeffects` ENGINE = InnoDB;
ALTER TABLE `kb3_moons` ENGINE = InnoDB;


-Jonas

[beansman]

I show two tables still as MyISAM, would it help to convert those over also? If so:

ALTER TABLE `kb3_dgmtypeeffects` ENGINE = InnoDB;
ALTER TABLE `kb3_moons` ENGINE = InnoDB;


-Jonas

Yes!

[Trent Angelus]

My DB seems slower after this. Really slow

I converted the table types, and added the indexes.
I went around to all the tables and merged the separate indexes into one, as Tribalize suggested.

http://www.eve-sci.dreamhosters.com/kb/?a=kills

[beansman]

My DB seems slower after this. Really slow

I converted the table types, and added the indexes.
I went around to all the tables and merged the separate indexes into one, as Tribalize suggested.

http://www.eve-sci.dreamhosters.com/kb/?a=kills

How much memory does your innodb have to run on?

[Guest]

so..

Do i use the query from the 1st post and:

ALTER TABLE `kb3_dgmtypeeffects` ENGINE = InnoDB;
ALTER TABLE `kb3_moons` ENGINE = InnoDB;

[Guest]

yes.. thats all you do.