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 » Fri Oct 10, 2008 20:06

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 » Fri Oct 10, 2008 20:17

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 » Tue Oct 14, 2008 00:20

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 » Tue Oct 14, 2008 10:49

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 » Tue Oct 14, 2008 16:03

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 » Tue Oct 14, 2008 19:25

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 » Tue Oct 14, 2008 23:51

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 » Wed Oct 15, 2008 07:18

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 » Thu Oct 16, 2008 03:33

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 » Thu Oct 16, 2008 19:27

yes.. thats all you do.

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 1 guest