Howto: Database optimizing

Thought of something everyone else is having trouble with and just wanna help them? Post it here.
hux
Greenhorn
Posts: 19
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by 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
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman »

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

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

Mike Wittman

Re: Howto: Database optimizing

Post by 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
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by 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!
/HyperBeanie

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

jonas vinthyn
Noob
Posts: 8
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by 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
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman »

jonas vinthyn wrote: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! ;)
/HyperBeanie

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

User avatar
Trent Angelus
Greenhorn
Posts: 24
Joined: Sat Sep 20, 2008 01:32
Location: EDK3.1:865

Re: Howto: Database optimizing

Post by 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
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman »

Trent Angelus wrote: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?
/HyperBeanie

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

Guest

Re: Howto: Database optimizing

Post by 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

Re: Howto: Database optimizing

Post by Guest »

yes.. thats all you do.

Post Reply