Limit CPU Usage

ZiaTioN · Post by **ZiaTioN** » Mon Feb 13, 2006 12:35 pm

When I analize my largest table (PACKS) it comes back with this info:

Table Op Msg_type Msg_text
IRCDIG.PACKS analyze status OK

Void Main · Post by **Void Main** » Mon Feb 13, 2006 1:04 pm

Sorry, poor choice of terms on my part. In phpMyAdmin in the table structure view under the "Size" column, click on the linked size value (22MB, 48.2KB, etc) next to the table you want to work with. You should see a link in the middle of the page that says "Propose Table Structure". What that will do is list the type of data you currently have in the table, max/min lengths, etc and propose what the optimum table structure would be for the data you have in that table.

ZiaTioN · Post by **ZiaTioN** » Mon Feb 13, 2006 1:25 pm

Ahh I got it, thanks.

It recommends enumeration type "enum" for some of the old text types that I have now chaged to varchar. For enumeration types I have to list the values? This would not be feasible for my usage since I am unaware of the value prior to adding the data.

Void Main · Post by **Void Main** » Mon Feb 13, 2006 1:50 pm

Yeah, I rarely use ENUM. I normally use char(x) or varchar but it you do just have a few small static items ENUM would probably be the wiser choice.

ZiaTioN · Post by **ZiaTioN** » Mon Feb 13, 2006 5:20 pm

Just for reference:

I managed to turn my db into a lean mean indexing machine (with some help here and from experts-exchange)! The biggest enhancement was from changing my heavily used tables to the InnoDB engine instead of the MyISAM engine. InnoDB does row-level locking instead of table-locking. This allowed my application to perform numerous task on the same table at the same time instead of one at a time queueing others and waiting for the lock to be returned.

I did add some indexes too and these seem to be helping a lot too. I first added indexes for the entire length but then changed them to just index the first 5 characters and this seemed to help a ton.

I also changed all my text fields to varchars. Oh and I did find out that you can index text fields, you just have to give a length but I did not use this method anyway.

Now my database runs consistantly around 3% to 11% but does spike to 20% every so often due to the front end searching with wildcards. I guess when wildcards (%) are used, no indexes can be used.

3% is a GIGANTIC improvement over 99.9%.

Thank God for people who know more than I do in certain areas (like databases).

Void Main · Post by **Void Main** » Mon Feb 13, 2006 7:22 pm

Good news. Yeah, optimizing your database/queries can improve your performance by ten thousand percent.